学步园

--計算當月天數及周次
SELECT rn, num_day,
       CASE
          WHEN MAX (rn)KEEP (DENSE_RANK LAST ORDER BY rn) OVER () = rn
             THEN nextday
          ELSE nextday - 1
       END nextday
  FROM (SELECT ROWNUM rn, num_day,LEAD (num_day, 1) OVER (ORDER BY num_day) nextday
          FROM (SELECT 1 num_day
                  FROM DUAL
                UNION
                SELECT num_day
                  FROM (SELECT ROWNUM num_day,
                               TRIM
                                  (TO_CHAR (TO_DATE (   '201002'|| TO_CHAR (ROWNUM, '00'),'yyyymmdd'),
                                            'day','NLS_DATE_LANGUAGE = english')
                                  ) eng_day
                          FROM user_objects a
                         WHERE ROWNUM <=TO_CHAR (LAST_DAY (TO_DATE ('201002','yyyymm')),'dd')) cal
                 WHERE eng_day = 'monday'
                UNION ALL
                SELECT TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE ('201002','yyyymm')),'dd'))
                  FROM DUAL))
 WHERE nextday IS NOT NULL