利用sql server直接创建日历

yzc164

　　看到网上有高手直接用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真是华丽的强大，强大的华丽啊。