利用sql server直接创建日历

看到网上有高手直接用sql查询创建日历，也想自己动手实践一遍。笔者这里的实现和网上的都没有什么区别，思路也没有什么新意。觉得好玩，就把它记下来吧。
一、准备知识
1、sql的with关键字
关于with和公用表表达式（CTE），可以参考 SQL Server 2005新特性之使用with关键字解决递归父子关系和
Sql Server2005 Transact-SQL 新兵器学习总结之-公用表表达式(CTE) 。
2、sql的pivot关键字
pivot非常强大，但是对于新手来说，可能连这个单词都很生僻，使用也是举步维艰。pivot的示例可以参考 这篇和 这篇。
二、实现
1、实现思路：
使用递归with子句，返回当前月的每一天，然后使用case和max转换为周内日期。
2、辅助表T1

USE   [ TestDb ]
GO
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
CREATE   TABLE   [ dbo ] . [ T1 ] (
     [ tid ]   [ int ]   NOT   NULL ,
  CONSTRAINT   [ PK_T1 ]   PRIMARY   KEY   CLUSTERED  
(
     [ tid ]   ASC
) WITH  (IGNORE_DUP_KEY  =   OFF )  ON   [ PRIMARY ]
)  ON   [ PRIMARY ]

说明：T1表中有且只有一条记录：insert into t1 values(1)
3、生成日历的sql

with  x(dy,dm,mth,dw,wk)
as (
select  dy, day (dy) dm, datepart (m,dy) mth, datepart (dw,dy) dw,
case   when   datepart (dw,dy) = 1
then   datepart (ww,dy) - 1
else   datepart (ww,dy)  end  wk
from  (  select   dateadd ( day , - day ( getdate ()) + 1 , getdate ()) dy  from  t1) x
union   all   select   dateadd (d, 1 ,dy), day ( dateadd (d, 1 ,dy)),mth,
datepart (dw, dateadd (d, 1 ,dy)),
case   when   datepart (dw, dateadd (d, 1 ,dy)) = 1
then   datepart (wk, dateadd (d, 1 ,dy)) - 1
else   datepart (wk, dateadd (d, 1 ,dy))  end
from  x  where   datepart (m, dateadd (d, 1 ,dy)) = mth)
select   max ( case  dw  when   2   then  dm  end )  as   ' 星期一 ' ,
        max ( case  dw  when   3   then  dm  end )  as   ' 星期二 ' ,
        max ( case  dw  when   4   then  dm  end )  as   ' 星期三 ' ,
        max ( case  dw  when   5   then  dm  end )  as   ' 星期四 ' ,
        max ( case  dw  when   6   then  dm  end )  as   ' 星期五 ' ,
        max ( case  dw  when   7   then  dm  end )  as   ' 星期六 ' ,
        max ( case  dw  when   1   then  dm  end )  as   ' 星期日 '
from  x  group   by  wk  order   by  wk

图片：
4、生成日历sql语句说明
（1）首先，为当前月的每一天返回一行信息。可以使用sql server支持递归with的with子句来实现。返回的每一行包含的信息：月份日期（dm），星期几（dw），当前月份（mth），iso周序号（wk）。
（2）在递归之前，递归视图x产生的结果（union all的上半部分）如下所示：

select  dy, day (dy) dm, datepart (m,dy) mth, datepart (dw,dy) dw,
case   when   datepart (dw,dy) = 1
then   datepart (ww,dy) - 1
else   datepart (ww,dy)  end  wk
from  (  select   dateadd ( day , - day ( getdate ()) + 1 , getdate ()) dy  from  t1) x

（3）下一步重复递增dm值（递增次数就是月份对应天数），直到超出当前月为止。在对当前月的每一天进行处理时，也会得到每天对应星期几以及当日的iso周序号。

with  x(dy,dm,mth,dw,wk)
as (
select  dy, day (dy) dm, datepart (m,dy) mth, datepart (dw,dy) dw,
case   when   datepart (dw,dy) = 1
then   datepart (ww,dy) - 1
else   datepart (ww,dy)  end  wk
from  (  select   dateadd ( day , - day ( getdate ()) + 1 , getdate ()) dy  from  t1) x
union   all   select   dateadd (d, 1 ,dy), day ( dateadd (d, 1 ,dy)),mth,
datepart (dw, dateadd (d, 1 ,dy)),
case   when   datepart (dw, dateadd (d, 1 ,dy)) = 1
then   datepart (wk, dateadd (d, 1 ,dy)) - 1
else   datepart (wk, dateadd (d, 1 ,dy))  end
from  x  where   datepart (m, dateadd (d, 1 ,dy)) = mth)
select   *
from  x 

此时，当前月的每一天包含的信息有：月份日期值，月份值，一位数字表示的星期几（1-7分别对应星期日到星期六）以及iso周序号。
（4）使用一个case表达式确定dm（当前月的每一天）中每个值对应星期几。

with  x(dy,dm,mth,dw,wk)
as (
select  dy, day (dy) dm, datepart (m,dy) mth, datepart (dw,dy) dw,
case   when   datepart (dw,dy) = 1
then   datepart (ww,dy) - 1
else   datepart (ww,dy)  end  wk
from  (  select   dateadd ( day , - day ( getdate ()) + 1 , getdate ()) dy  from  t1) x
union   all   select   dateadd (d, 1 ,dy), day ( dateadd (d, 1 ,dy)),mth,
datepart (dw, dateadd (d, 1 ,dy)),
case   when   datepart (dw, dateadd (d, 1 ,dy)) = 1
then   datepart (wk, dateadd (d, 1 ,dy)) - 1
else   datepart (wk, dateadd (d, 1 ,dy))  end
from  x  where   datepart (m, dateadd (d, 1 ,dy)) = mth)
select   case  dw  when   2   then  dm  end   as   ' 星期一 ' ,
        case  dw  when   3   then  dm  end   as   ' 星期二 ' ,
        case  dw  when   4   then  dm  end   as   ' 星期三 ' ,
        case  dw  when   5   then  dm  end   as   ' 星期四 ' ,
        case  dw  when   6   then  dm  end   as   ' 星期五 ' ,
        case  dw  when   7   then  dm  end   as   ' 星期六 ' ,
        case  dw  when   1   then  dm  end   as   ' 星期日 '
from  x 

这里每周的每一天都独占一行，在每行中，包含日期编号的列都与星期名相对应。
（5） 最后把每周的所有日期放在一行中。
正如本文3中给出的最终sql语句一样，对各列使用聚集函数max，并且按照周序号wk分组排序即可。
ps：最终结果在sql server2005下测试通过，其他版本未测试。 不过sql server 2005版本下利用dbms自带的函数pivot可以很轻松实现日历的：

Use  testdb
go

Declare  
     @Date   datetime ,
     @StartDate   datetime ,
     @EndDate   datetime ,
     @FirstIndex   int

Set   @Date   = getdate ()  -- 输入一个日期，即可算出当月的日历 比如输入20080808,这里取当前日期

Select  
     @StartDate = Convert ( char ( 6 ), @Date , 112 ) + ' 01 ' , 
     @EndDate = Dateadd ( month , 1 , @StartDate ) - 1 ,
     @FirstIndex = Datediff ( day , 0 , @StartDate ) % 7  ;
With  t  As
(
     Select  Date = Convert ( int , 1 ),Row = ( @FirstIndex ) / 7 ,Col = @FirstIndex
     Union   All
     Select  Date = Date + 1 ,Row = ( @FirstIndex + Date) / 7 ,Col = (Date + @FirstIndex ) % 7
     From  t
     Where  Date <= Datediff ( day , @StartDate , @EndDate )
)
Select  
     [ 星期一 ] = Isnull ( Convert ( char ( 2 ), [ 0 ] ), '' ),
     [ 星期二 ] = Isnull ( Convert ( char ( 2 ), [ 1 ] ), '' ),
     [ 星期三 ] = Isnull ( Convert ( char ( 2 ), [ 2 ] ), '' ),
     [ 星期四 ] = Isnull ( Convert ( char ( 2 ), [ 3 ] ), '' ),
     [ 星期五 ] = Isnull ( Convert ( char ( 2 ), [ 4 ] ), '' ),
     [ 星期六 ] = Isnull ( Convert ( char ( 2 ), [ 5 ] ), '' ),
     [ 星期日 ] = Isnull ( Convert ( char ( 2 ), [ 6 ] ), '' )
From  t
Pivot ( Max (Date)  For  col  In ( [ 0 ] , [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] , [ 5 ] , [ 6 ] )) b

pivot真是华丽的强大，强大的华丽啊。