sql語句建立日曆

http://www.itpub.net/thread-469543-1-1.html

2005年的帖子：

語句如下：

/* Formatted on 2010/06/20 15:13 (Formatter Plus v4.8.8) */
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
/