首页 > 数据库技术 > 详细

ORACLE按每月、每周关联数据表

时间:2017-12-07 12:45:18      阅读:229      评论:0      收藏:0      [点我收藏+]
-- 时间段内的每个月份,{0}:开始日期,{1}:结束日期
SELECT TO_CHAR(ADD_MONTHS(TO_DATE({0}, YYYY-MM), ROWNUM - 1),
                           YYYYMM) AS MONTHLIST
              FROM DUAL
            CONNECT BY ROWNUM <=
                       MONTHS_BETWEEN(TO_DATE({1}, YYYY-MM),
                                      TO_DATE({0}, YYYY-MM)) + 1

例: SELECT TO_CHAR(ADD_MONTHS(TO_DATE(
2017-01, YYYY-MM), ROWNUM - 1), YYYYMM) AS MONTHLIST FROM DUAL CONNECT BY ROWNUM <= MONTHS_BETWEEN(TO_DATE(2017-06, YYYY-MM), TO_DATE(2017-01, YYYY-MM)) + 1 执行结果: MONTHLIST 1 201701 2 201702 3 201703 4 201704 5 201705 6 201706
-- 时间段内的每周,{0}:开始时间,{1}:结束时间
SELECT TO_CHAR(TO_DATE({0}, YYYY-MM-DD) + (ROWNUM - 1) * 7,
                           IYYYIW) AS WEEKLIST
              FROM DUAL
            CONNECT BY ROWNUM <=
                       (TRUNC(TO_DATE({1}, YYYY-MM-DD), IW) -
                       TRUNC(TO_DATE({0}, YYYY-MM-DD), IW)) / 7 + 1

例:
SELECT TO_CHAR(TO_DATE(2017-04-25, YYYY-MM-DD) + (ROWNUM - 1) * 7,
                           IYYYIW) AS WEEKLIST
              FROM DUAL
            CONNECT BY ROWNUM <=
                       (TRUNC(TO_DATE(2017-12-01, YYYY-MM-DD), IW) -
                       TRUNC(TO_DATE(2017-04-25, YYYY-MM-DD), IW)) / 7 + 1

执行结果:
       WEEKLIST
1    201717
2    201718
3    201719
4    201720
5    201721
6    201722
7    201723
8    201724
9    201725
10    201726
11    201727
12    201728
13    201729
14    201730
15    201731
16    201732
17    201733
18    201734
19    201735
20    201736
21    201737
22    201738
23    201739
24    201740
25    201741
26    201742
27    201743
28    201744
29    201745
30    201746
31    201747
32    201748

记录完毕

ORACLE按每月、每周关联数据表

原文:http://www.cnblogs.com/LLSutdy/p/7998322.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!