Oracle用SQL列印日曆

lhrbest發表於2017-04-13
原文網址 : http://blog.itpub.net/26736162/viewspace-2137224/
OracleSQL

Oracle用SQL列印日曆


1.1  列印當月日曆

SELECT MAX(DECODE(DOW, 1, D, NULL)) SUN,

       MAX(DECODE(DOW, 2, D, NULL)) MON,

       MAX(DECODE(DOW, 3, D, NULL)) TUE,

       MAX(DECODE(DOW, 4, D, NULL)) WED,

       MAX(DECODE(DOW, 5, D, NULL)) THU,

       MAX(DECODE(DOW, 6, D, NULL)) FRI,

       MAX(DECODE(DOW, 7, D, NULL)) SAT

  FROM (SELECT ROWNUM D,

               ROWNUM - 2 + TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'D')) P,

               TO_CHAR(TRUNC(SYSDATE, 'MM') - 1 + ROWNUM, 'D') DOW

          FROM ALL_OBJECTS

         WHERE ROWNUM <=

               TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SYSDATE)), 'DD')))

 GROUP BY TRUNC(P / 7)

 ORDER BY sun NULLS FIRST;

wps2289.tmp 

 

 

1.2  列印年曆

SELECT CASE

         WHEN (NEW_YWEEK = MIN(NEW_YWEEK)

               OVER(PARTITION BY MON ORDER BY NEW_YWEEK)) THEN

          MON_NAME

         ELSE

          NULL

       END AS MONTH,

       NEW_YWEEK AS YWEEK,

       ROW_NUMBER() OVER(PARTITION BY MON ORDER BY NEW_YWEEK) AS MWEEK,

       SUM(DECODE(WDAY, '1', MDAY, NULL)) AS SUN,

       SUM(DECODE(WDAY, '2', MDAY, NULL)) AS MON,

       SUM(DECODE(WDAY, '3', MDAY, NULL)) AS TUE,

       SUM(DECODE(WDAY, '4', MDAY, NULL)) AS WED,

       SUM(DECODE(WDAY, '5', MDAY, NULL)) AS THU,

       SUM(DECODE(WDAY, '6', MDAY, NULL)) AS FRI,

       SUM(DECODE(WDAY, '7', MDAY, NULL)) AS SAT

  FROM (SELECT DAYOFYEAR AS EVERYDAY,

               TO_CHAR(DAYOFYEAR, 'mm') AS MON,

               TO_CHAR(DAYOFYEAR, 'Month') AS MON_NAME,

               TO_CHAR(DAYOFYEAR, 'w') AS MWEEK,

               TO_CHAR(DAYOFYEAR, 'ww') AS YWEEK,

               CASE

                 WHEN (TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd') > '1') AND

                      (TO_CHAR(DAYOFYEAR, 'd') <

                      TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd')) THEN

                  TO_CHAR(TO_CHAR(DAYOFYEAR, 'ww') + 1, 'fm00')

                 ELSE

                  TO_CHAR(DAYOFYEAR, 'ww')

               END AS NEW_YWEEK,

               TO_CHAR(DAYOFYEAR, 'd') AS WDAY,

               TO_CHAR(DAYOFYEAR, 'dd') AS MDAY

          FROM (SELECT TO_DATE(&YEAR || '0101', 'yyyymmdd') + LEVEL - 1 AS DAYOFYEAR

                  FROM DUAL

                CONNECT BY LEVEL <=

                           TO_CHAR(TO_DATE(&YEAR || '1231', 'yyyymmdd'),

                                   'ddd')))

 GROUP BY MON, MON_NAME, NEW_YWEEK;

wps228A.tmp 

wps229B.tmp 

1.3  列印某月日曆

1.3.1  方法1

ALTER session set nls_language='SIMPLIFIED CHINESE';

select  to_char(everyday,'ww') as week,

        sum(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd'))) as 星期日,

        sum(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd'))) as 星期一,

        sum(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd'))) as 星期二,

        sum(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd'))) as 星期三,

        sum(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd'))) as 星期四,

        sum(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd'))) as 星期五,

        sum(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd'))) as 星期六

    from(select to_date('20170301','yyyymmdd') + level - 1 as everyDay

          from dual

          connect by level <= (last_day(to_date('20170301','yyyymmdd')) - to_date('20170301','yyyymmdd') +1)

        )

  group by to_char(everyday,'ww')

  ORDER BY week;

wps229C.tmp 

 

1.3.2  方法2

select  to_char(everyday,'ww') as week,

        sum(decode(to_char(everyday,'d'),'1',to_char(everyday,'dd'))) as 星期日,

        sum(decode(to_char(everyday,'d'),'2',to_char(everyday,'dd'))) as 星期一,

        sum(decode(to_char(everyday,'d'),'3',to_char(everyday,'dd'))) as 星期二,

        sum(decode(to_char(everyday,'d'),'4',to_char(everyday,'dd'))) as 星期三,

        sum(decode(to_char(everyday,'d'),'5',to_char(everyday,'dd'))) as 星期四,

        sum(decode(to_char(everyday,'d'),'6',to_char(everyday,'dd'))) as 星期五,

        sum(decode(to_char(everyday,'d'),'7',to_char(everyday,'dd'))) as 星期六

    from(select to_date('20170301','yyyymmdd') + level - 1 as everyDay

          from dual

          connect by level <= (last_day(to_date('20170301','yyyymmdd')) - to_date('20170301','yyyymmdd') +1)

        )

  group by to_char(everyday,'ww')

  ORDER BY week;

wps229D.tmp 


http://jackywood.itpub.net/post/1369/127565

一條SQL語句生成年曆。
  1. select case
  2.            when (new_yweek = min(new_yweek)over(partition by mon order by new_yweek)) then
  3.              mon_name
  4.            else
  5.              null
  6.          end as month,
  7.          new_yweek as yweek,
  8.          row_number() over(partition by mon order by new_yweek) as mweek,
  9.          sum(decode(wday, '1', mday, null)) as sun,
  10.          sum(decode(wday, '2', mday, null)) as mon,
  11.          sum(decode(wday, '3', mday, null)) as tue,
  12.          sum(decode(wday, '4', mday, null)) as wed,
  13.          sum(decode(wday, '5', mday, null)) as thu,
  14.          sum(decode(wday, '6', mday, null)) as fri,
  15.          sum(decode(wday, '7', mday, null)) as sat
  16.     from (select dayofyear as everyday,
  17.                  to_char(dayofyear, 'mm') as mon,
  18.                  to_char(dayofyear, 'Month') as mon_name,
  19.                  to_char(dayofyear, 'w') as mweek,
  20.                  to_char(dayofyear, 'ww') as yweek,
  21.                  case
  22.                    when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and
  23.                         (to_char(dayofyear, 'd') <
  24.                         to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then
  25.                     to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
  26.                    else
  27.                     to_char(dayofyear, 'ww')
  28.                  end as new_yweek,
  29.                  to_char(dayofyear, 'd') as wday,
  30.                  to_char(dayofyear, 'dd') as mday
  31.             from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
  32.                     from dual
  33.                   connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
  34.                  )
  35.          )
  36.    group by mon, mon_name, new_yweek
  37.   /
連結是作者的解讀，我感覺年曆的實現主要有三個步驟
1.生成一年之中所有的日期
2.在上步的基礎上，得到每個日期所在周、月、年的具體資訊。
3.行列轉置

其中new_yweek這部分看的真是雲裡霧裡，它主要解決Oracle to_char函式IW和WW坑爹的問題。
  1. select to_char(d,'yyyy-mm-dd'),to_char(d,'d') dayofweek,to_char(d,'WW') WW,to_char(d,'IW') IW    from (     
  2. select to_date('20131229','yyyymmdd')+level-1 as d from dual connect by level<=10);

TO_CHAR(D, D WW IW           
---------- - -- --
2013-12-29 1 52 52
2013-12-30 2 52 01
2013-12-31 3 53 01
2014-01-01 4 01 01
2014-01-02 5 01 01
2014-01-03 6 01 01
2014-01-04 7 01 01
2014-01-05 1 01 01
2014-01-06 2 01 02
2014-01-07 3 01 02

已選擇10行。


對比日曆，發現2013年12月30,31日的IW，均劃分到了2014年的第一週

MONTH  YWE MWEEK   SUN   MON   TUE   WED   THU   FRI   SAT
------ --- ----- ----- ----- ----- ----- ----- ----- -----
12月   49      1     1     2     3     4     5     6     7
       50      2     8     9    10    11    12    13    14
       51      3    15    16    17    18    19    20    21
       52      4    22    23    24    25    26    27    28
       53      5    29    30    31

1）ww的演算法為每年1月1日為第一週開始，date+6為每一週結尾

　　例如20050101為第一週的第一天，而第一週的最後一天為20050101+6=20050107

　　公式 每週第一天 ：date + 周 * 7 - 7

　　每週最後一天：date + 周 * 7 - 1

2）iw的演算法為星期一至星期日算一週，且每年的第一個星期一為第一週，

　　 例如20050101為星期六，所以用iw的演算法是前年的53周，而20050103之後才是第一週的開始。

　　公式 每週第一天 ：next_day(date) + 周 * 7 - 7

　　每週最後一天：next_day(date) + 周 * 7 - 1


按照上述演算法，WW的結果相差較遠，而IW比較貼近我們對於日期的認識。
但是問題是IW存在邊界問題。它會認為12月30日和31日是2014年的第一週，以此統計年曆，則會出現問題。所以new_yweek解決的應該是這個問題。

可以使用下面的方法解決IW邊界問題。
  1. select case
  2.            when (yweek = min(yweek)over(partition by mon order by yweek)) then
  3.              mon_name
  4.            else
  5.              null
  6.          end as month,
  7.          yweek as yweek,
  8.          row_number() over(partition by mon order by yweek) as mweek,
  9.          sum(decode(wday, '1', mday, null)) as sun,
  10.          sum(decode(wday, '2', mday, null)) as mon,
  11.          sum(decode(wday, '3', mday, null)) as tue,
  12.          sum(decode(wday, '4', mday, null)) as wed,
  13.          sum(decode(wday, '5', mday, null)) as thu,
  14.          sum(decode(wday, '6', mday, null)) as fri,
  15.          sum(decode(wday, '7', mday, null)) as sat
  16.     from (select dayofyear as everyday,
  17.                  to_char(dayofyear, 'mm') as mon,
  18.                  to_char(dayofyear, 'Month') as mon_name,
  19.                  to_char(dayofyear, 'w') as mweek,
  20.                  max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear, 'iw')+1,to_char(dayofyear, 'iw'))) over(order by dayofyear) as yweek,
  21.                  to_char(dayofyear, 'd') as wday,
  22.                  to_char(dayofyear, 'dd') as mday
  23.             from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
  24.                     from dual
  25.                   connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
  26.                  )
  27.          )
  28.    group by mon, mon_name, yweek
  29.   /


解決IW邊界問題:
max(decode(to_char(dayofyear,'d'),'1',to_char(dayofyear, 'iw')+1,to_char(dayofyear, 'iw'))) over(order by dayofyear) as yweek
decode部分，如果日期是週日，則將iw的值+1，以便日曆對齊。
而max分析函式部分，解決類似12月30日，31日劃分到下一年的問題。
這個實現存在一些問題。
如果元旦是週五，週六或者週日，例如20110101，它是週六，IW會認為這天是2010年的第五十二週。

  1. SQL> select to_char(to_date('20110101','yyyymmdd'),'IW') from dual;

  3. TO
  4. --
  5. 52
對於這個問題，我沒有解決的方法，但是一個同事有另外一個方案，用自定義的周序列，使用偏移量。

  1. select case
  2.            when (yweek = min(yweek)over(partition by mon order by yweek)) then
  3.              mon_name
  4.            else
  5.              null
  6.          end as month,
  7.          yweek as yweek,
  8.          row_number() over(partition by mon order by yweek) as mweek,
  9.          sum(decode(wday, '1', mday, null)) as sun,
  10.          sum(decode(wday, '2', mday, null)) as mon,
  11.          sum(decode(wday, '3', mday, null)) as tue,
  12.          sum(decode(wday, '4', mday, null)) as wed,
  13.          sum(decode(wday, '5', mday, null)) as thu,
  14.          sum(decode(wday, '6', mday, null)) as fri,
  15.          sum(decode(wday, '7', mday, null)) as sat
  16.     from (select dayofyear as everyday,
  17.                  to_char(dayofyear, 'mm') as mon,
  18.                  to_char(dayofyear, 'Month') as mon_name,
  19.                  to_char(dayofyear, 'w') as mweek,
  20.                  ceil(to_number(dayIndex)/7) as yweek,
  21.                  to_char(dayofyear, 'd') as wday,
  22.                  to_char(dayofyear, 'dd') as mday
  23.             from (
  24.                     select 
  25.                     to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear,
  26.                     to_char(s.firstday,'d')+rownum dayIndex
  27.                     from 
  28.                     dual,(select to_date(&year || '0101','yyyymmdd')-1 firstday from dual) s
  29.                     connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')
  30.                  )
  31.          )
  32.    group by mon, mon_name, yweek
  33.   /
    
  1. case when   (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') 
  2.             and
  3.             (to_char(dayofyear, 'd') < to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) 
  4.      then
  5.             to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
  6.      else
  7.             to_char(dayofyear, 'ww')
  8. end as new_yweek
以2013年一月為例，元旦是週二（不是週日，滿足了Case When的第一個條件）
6,7日是週日、週一，小於元旦的週二，所以所在周+1，那麼行列轉置後在日曆上就下沉了一層。



1 要構造某年某月的日曆，必須先知道這個月的開始時間，結束時間及天數
開始日期 例如 2006年11月
select to_date('20061101','yyyymmdd') as startDayOfMon from  dual;

結束日期 
select last_day(to_date('20061101','yyyymmdd')) as endDayOfMon from  dual;

日期區間天數
select last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1 as DayOfMon
  from dual;

2 接下來就是需要得到開始時間到結束時間每一天的結果集
select * from (
select to_date('20061101','yyyymmdd') + level - 1 as everyDay from dual
  connect by level <= 
  (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1));

3 再進一步則是將該月中的日期分解成第幾周，星期幾。

select everyDay,to_char(everyday,'yyyy') as 年,
   to_char(everyday,'mm') as 月,
   to_char(everyday,'dd') as 日,
   to_char(everyday,'dy') as 星期幾,
   lpad(to_char(everyday,'w'),6) as 該月的第幾周,
   lpad(to_char(everyday,'ww'),6) as 該年的第幾周
   from(select to_date('20061101','yyyymmdd') + level - 1 as everyDay from dual
  connect by level <= 
  (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1));

4 這個結果集求出來後,接下拉就是使用DECODE函式進行行列轉換了
select everyDay,to_char(everyday,'yyyy') as 年,
   to_char(everyday,'mm') as 月,
   to_char(everyday,'dd') as 日,
   to_char(everyday,'dy') as 星期幾,
   lpad(to_char(everyday,'w'),6) as 該月的第幾周,
   lpad(to_char(everyday,'ww'),6) as 該年的第幾周,
   lpad(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd')),3) as 星期日,
   lpad(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd')),3) as 星期一,
   lpad(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd')),3) as 星期二,
   lpad(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd')),3) as 星期三,
   lpad(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd')),3) as 星期四,
   lpad(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd')),3) as 星期五,
   lpad(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd')),3) as 星期六
    from(select to_date('20061101','yyyymmdd') + level - 1 as everyDay from dual
  connect by level <= 
  (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1));

5 再進一步就是統計彙總了,大家發現一個小問題沒有？
  就是該月的第幾周這裡是按本月開始是星期幾為開始的日期，很有意思，
  這樣我們按該日是該年的第幾周則是以今年開始日期是星期幾為開始日期

select  to_char(everyday,'w') as week,
        sum(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd'))) as 星期日,
        sum(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd'))) as 星期一,
        sum(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd'))) as 星期二,
        sum(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd'))) as 星期三,
        sum(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd'))) as 星期四,
        sum(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd'))) as 星期五,
        sum(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd'))) as 星期六
    from(select to_date('20061101','yyyymmdd') + level - 1 as everyDay 
          from dual
          connect by level <= (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1)
        )
  group by to_char(everyday,'w');

6 以上日曆基本成功，但還有一個問題，就是一週的開始時間問題

select  to_char(everyday,'ww') as week,
        sum(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd'))) as 星期日,
        sum(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd'))) as 星期一,
        sum(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd'))) as 星期二,
        sum(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd'))) as 星期三,
        sum(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd'))) as 星期四,
        sum(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd'))) as 星期五,
        sum(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd'))) as 星期六
    from(select to_date('20061101','yyyymmdd') + level - 1 as everyDay 
          from dual
          connect by level <= (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1)
        )
  group by to_char(everyday,'ww');

7  這樣雖然可以解決，但還存在問題，大家可以考慮下！也可以考慮下年曆怎麼做！

select  ceil((to_char(everyday,'dd')+(to_char(to_date('20061101','yyyymmdd'),'d')-1))/7) as week,
        sum(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd'))) as 星期日,
        sum(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd'))) as 星期一,
        sum(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd'))) as 星期二,
        sum(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd'))) as 星期三,
        sum(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd'))) as 星期四,
        sum(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd'))) as 星期五,
        sum(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd'))) as 星期六
    from(select to_date('20061101','yyyymmdd') + level - 1 as everyDay 
          from dual
          connect by level <= (last_day(to_date('20061101','yyyymmdd')) - to_date('20061101','yyyymmdd') +1)
        )
  group by ceil((to_char(everyday,'dd')+(to_char(to_date('20061101','yyyymmdd'),'d')-1))/7);

   以上是最終的結果。






About Me

...............................................................................................................................

● 本文整理自網路

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

Oracle用SQL列印日曆
 DBA筆試面試講解
 歡迎與我聯絡

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2137224/，如需轉載，請註明出處，否則將追究法律責任。

相關文章