數據庫環境：SQL SERVER 2008R2

我之前有寫過打印本月日歷的SQL，里頭有詳細的說明。具體請參考前面的博文——生成本月日歷。

全年日歷只是在本月日歷的基礎上加了月信息，并按月份分組求得。

下面直接分享SQL

/*基礎數據：年初日期，全年有多少天*/WITH    x0          AS ( SELECT   CONVERT(DATE, '2015-01-01') AS yearbegin ,                        CONVERT(DATE, '2015-12-31') AS yearend ,                        DATEDIFF(DAY, '2015-01-01', '2015-12-31') AS dayscount             ),/*枚舉全年的所有日期*/        x1          AS ( SELECT   DATEADD(DAY, number, yearbegin) AS ndate               FROM     x0 ,                        master.dbo.spt_values spt               WHERE    spt.type = 'P'                        AND spt.number >= 0                        AND spt.number <= dayscount             ),/*羅列全年日期對應的月份，第幾周，星期幾，本月第幾天*/        x2          AS ( SELECT   ndate ,                        DATEPART(month, ndate) AS nmonth ,                        DATEPART(week, ndate) AS nweek ,                        DATEPART(weekday, ndate) AS nweekday ,                        DATEPART(day, ndate) AS nday               FROM     x1             ),/*按月份、所在周分組，生成全年日歷*/        x3          AS ( SELECT   nmonth ,                        nweek ,                        ISNULL(CAST(MAX(CASE nweekday                                          WHEN 1 THEN nday                                        END) AS VARCHAR(2)), '') AS 日 ,                        ISNULL(CAST(MAX(CASE nweekday                                          WHEN 2 THEN nday                                        END) AS VARCHAR(2)), '') AS 一 ,                        ISNULL(CAST(MAX(CASE nweekday                                          WHEN 3 THEN nday                                        END) AS VARCHAR(2)), '') AS 二 ,                        ISNULL(CAST(MAX(CASE nweekday                                          WHEN 4 THEN nday                                        END) AS VARCHAR(2)), '') AS 三 ,                        ISNULL(CAST(MAX(CASE nweekday                                          WHEN 5 THEN nday                                        END) AS VARCHAR(2)), '') AS 四 ,                        ISNULL(CAST(MAX(CASE nweekday                                          WHEN 6 THEN nday                                        END) AS VARCHAR(2)), '') AS 五 ,                        ISNULL(CAST(MAX(CASE nweekday                                          WHEN 7 THEN nday                                        END) AS VARCHAR(2)), '') AS 六               FROM     x2               GROUP BY nmonth ,                        nweek             )/*將月份相同的值只在第一行顯示*/    SELECT  REPLACE(CASE WHEN ROW_NUMBER() OVER ( PARTITION BY nmonth ORDER BY nweek ) = 1                         THEN nmonth                         ELSE -1                    END, -1, '') AS 月份 ,            日 ,            一 ,            二 ,            三 ,            四 ,            五 ,            六    FROM    x3

代碼不算多，60多行，而且也好理解。如果覺得把“周日”放在第一列有點別扭，可以x2中生成所在周時對周日

做一些特別處理就可以了。

貼一下結果

（本文完）