利用sql server直接建立日曆

iSQlServer發表於2009-11-05
SQLServer
看到網上有高手直接用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
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtUSE [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
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtwith 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的上半部分）如下所示：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtselect 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周序號。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtwith 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（當前月的每一天）中每個值對應星期幾。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtwith 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可以很輕鬆實現日曆的：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtUse 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真是華麗的強大，強大的華麗啊。

原文地址：http://www.cnblogs.com/wjfluisfigo/archive/2009/11/04/1512810.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-618194/，如需轉載，請註明出處，否則將追究法律責任。

相關文章