你的位置:首页 > 数据库

[数据库]SQL打印全年日历


数据库环境: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 0          END, 0, '') AS 月份 ,      日 ,      一 ,      二 ,      三 ,      四 ,      五 ,      六  FROM  x3

代码不算多,60多行,而且也好理解。如果觉得把“周日”放在第一列有点别扭,可以x2中生成所在周时对周日

做一些特别处理就可以了。

(本文完)