在sqlservercentral上看到一个挑战sql的题目，
原文地址:http://beyondrelational.com/puzzles/tsql/61/english/generate-an-html-calendar-in-tsql.aspx
实现一个日历：
代码如下：
IF OBJECT_ID('TC61_Quiz','U') IS NOT NULL BEGIN
DROP TABLE TC61_Quiz
END
GO
CREATE TABLE TC61_Quiz(
ID INT IDENTITY,
QuizName VARCHAR(255)
)
GO
INSERT INTO TC61_Quiz(QuizName)
SELECT 'TSQL Quiz 2011'
SELECT * FROM TC61_Quiz
GO
IF OBJECT_ID('TC61_Questions','U') IS NOT NULL BEGIN
DROP TABLE TC61_Questions
END
GO
CREATE TABLE TC61_Questions(
ID INT,
Date DATETIME,
[User] VARCHAR(100)
)
GO
INSERT INTO TC61_Questions(ID,Date,[User])
SELECT 1,'2011-03-01','VinodKumar' UNION ALL
SELECT 1,'2011-03-02','Jacobsebastian' UNION ALL
SELECT 1,'2011-03-03','Madhivanan' UNION ALL
SELECT 1,'2011-03-04','PeterLarsson' UNION ALL
SELECT 1,'2011-03-05','Sergejack' UNION ALL
SELECT 1,'2011-03-06','JesseRoberge' UNION ALL
SELECT 1,'2011-03-07','RobertPearl' UNION ALL
SELECT 1,'2011-03-08','DavidBarbarin' UNION ALL
SELECT 1,'2011-03-09','MichaelColes' UNION ALL
SELECT 1,'2011-03-10','RiteshShah' UNION ALL
SELECT 1,'2011-03-11','RuiCarvalho' UNION ALL
SELECT 1,'2011-03-12','PinalDave' UNION ALL
SELECT 1,'2011-03-13','LeszekGniadkowski' UNION ALL
SELECT 1,'2011-03-14','LutzMueller' UNION ALL
SELECT 1,'2011-03-15','NakulVachhrajani' UNION ALL
SELECT 1,'2011-03-16','MikeLewis' UNION ALL
SELECT 1,'2011-03-17','TejasShah' UNION ALL
SELECT 1,'2011-03-18','FabienContaminard' UNION ALL
SELECT 1,'2011-03-19','RamiReddy' UNION ALL
SELECT 1,'2011-03-20','StefanGustafsson' UNION ALL
SELECT 1,'2011-03-21','GogulaAryalingam' UNION ALL
SELECT 1,'2011-03-22','AlejandroMesa' UNION ALL
SELECT 1,'2011-03-23','SatyaJayanty' UNION ALL
SELECT 1,'2011-03-24','ChintakChhapia' UNION ALL
SELECT 1,'2011-03-25','SanjayBhatia' UNION ALL
SELECT 1,'2011-03-26','NaomiNosonovsky' UNION ALL
SELECT 1,'2011-03-27','SankarReddy' UNION ALL
SELECT 1,'2011-03-28','NiladriBiswas' UNION ALL
SELECT 1,'2011-03-29','MadhuNair' UNION ALL
SELECT 1,'2011-03-30','VidhyaSagar' UNION ALL
SELECT 1,'2011-03-31','KentWaldrop'
SELECT * FROM TC61_Questions
GO
SELECT * FROM TC61_Quiz
SELECT * FROM TC61_Questions
--实现
DECLARE @s varchar(max),@ttime datetime,@i int ,@j int
SET @ttime='2011-03-02'
set @i=DATEPART(dw,convert(varchar(7),@ttime,120)+'-01')-1
set @j=DATEPART(dw,dateadd(mm,1,convert(varchar(8),@ttime,120)+'01')-1)%7
SET @s='<html><table border="1" cellpadding="3" cellspacing="4"><tr><td align="center" colspan="7">'
set @s=@s+ cast(month(@ttime) as varchar(2))+' '+ cast(year(@ttime) as varchar(2))
SET @s=@s+'</td>
</tr>
<tr>
<td align="center">Sun</td>
<td align="center">Mon</td>
<td align="center">Tue</td>
<td align="center">Wed</td>
<td align="center">Thu</td>
<td align="center">Fri</td>
<td align="center">Sat</td>
</tr>
'
declare @s1 varchar(max),@s2 varchar(max)
select @s1='',@s2=''
while @i>0
BEGIN
set @s1+='<td></td>'
set @i=@i-1
end
while 7-@j>0
begin
set @s2+='<td></td>'
set @j+=1
end
DECLARE @s3 varchar(max),@s4 varchar(max)
select @s3='',@s4=''
select @s3=@s1+convert(varchar(max),(select [user] "@Title" ,day(date) from TC61_Questions
for XML PATH('td')))+@s2
select @s4+='<tr>'+col1+'</tr>' FROM dbo.split(@s3,7)
select @s4=@s+@s4+'</table></html>'
SELECT cast (@s4 as xml)
--create function
--alter function split(@s varchar(max),@i int)
--RETURNS @ta table(
--col1 varchar(max)
--)
--as
--begin
--declare @k int ,@j int ,@l int,@p int
--SELECT @k = 1
-- , @j = 1
-- , @l = 0
-- while @k<=len(@s)
-- begin
-- set @j=1
-- while @j<=@i
-- begin
-- if charindex('</td>',@s,@l+5)>0
-- begin
-- set @l=charindex('</td>',@s,@l+5)
-- end
-- else
-- begin
-- set @l=len(@s)
-- break
-- end
-- set @j+=1
-- end
--insert into @ta
--SELECT substring(@s, @k, @l + 5 - @k)
-- set @k=@l+5
-- end
-- return
--end
方法二：和老外的要求应该差不多了。
SET DATEFIRST 7 ;
WITH cte
AS ( SELECT DATENAME(dw, [date]) AS week ,
DATENAME(ww, [date]) AS wk ,
DATENAME(DAY, [date]) AS [DDay] ,
*
FROM [db1].[dbo].[TC61_Questions]
),
cteTemp AS ( SELECT [wk] ,
ISNULL(MAX(CASE WHEN [week] = 'Sunday' THEN [DDay]
END), ' ') AS 'Sunday' ,
ISNULL(MAX(CASE WHEN [week] = 'Sunday' THEN [User]
END), ' ') AS 'SundayDesc' ,
ISNULL(MAX(CASE WHEN [week] = 'Monday' THEN [DDay]
END), ' ') AS 'Monday' ,
ISNULL(MAX(CASE WHEN [week] = 'Monday' THEN [User]
END), ' ') AS 'MondayDesc' ,
ISNULL(MAX(CASE WHEN [week] = 'Tuesday' THEN [DDay]
END), ' ') AS 'Tuesday' ,
ISNULL(MAX(CASE WHEN [week] = 'Tuesday' THEN [User]
END), ' ') AS 'TuesdayDesc' ,
ISNULL(MAX(CASE WHEN [week] = 'Wednesday' THEN [DDay]
END), ' ') AS 'Wednesday' ,
ISNULL(MAX(CASE WHEN [week] = 'Wednesday' THEN [User]
END), ' ') AS 'WednesdayDesc' ,
ISNULL(MAX(CASE WHEN [week] = 'Thursday' THEN [DDay]
END), ' ') AS 'Thursday' ,
ISNULL(MAX(CASE WHEN [week] = 'Thursday' THEN [User]
END), ' ') AS 'ThursdayDesc' ,
ISNULL(MAX(CASE WHEN [week] = 'Friday' THEN [DDay]
END), ' ') AS 'Friday' ,
ISNULL(MAX(CASE WHEN [week] = 'Friday' THEN [User]
END), ' ') AS 'FridayDesc' ,
ISNULL(MAX(CASE WHEN [week] = 'Saturday' THEN [DDay]
END), ' ') AS 'Saturday' ,
ISNULL(MAX(CASE WHEN [week] = 'Saturday' THEN [User]
END), ' ') AS 'SaturdayDesc'
FROM cte
GROUP BY [wk]
)
SELECT [html] = ( SELECT [tr] = ( SELECT [td/@align] = 'Center' ,
[td/@colspan] = '7' ,
[td] = 'March 2011'
FOR
XML PATH('') ,
TYPE
) ,
[*] = ( SELECT [td/@title] = SundayDesc ,
[td] = Sunday ,
NULL ,
[td/@title] = MondayDesc ,
[td] = Monday ,
NULL ,
[td/@title] = TuesdayDesc ,
[td] = Tuesday ,
NULL ,
[td/@title] = WednesdayDesc ,
[td] = Wednesday ,
NULL ,
[td/@title] = ThursdayDesc ,
[td] = Thursday ,
NULL ,
[td/@title] = FridayDesc ,
[td] = Friday ,
NULL ,
[td/@title] = SaturdayDesc ,
[td] = Saturday ,
NULL
FROM cteTemp
FOR
XML PATH('tr') ,
TYPE
)
FOR
XML PATH('table') ,
TYPE
)
FOR XML PATH('') ,
TYPE
结果：
<html>
<table>
<tr>
<td align="Center" colspan="7">March 2011</td>
</tr>
<tr>
<td title=" "> </td>
<td title=" "> </td>
<td title="VinodKumar">1</td>
<td title="Jacobsebastian">2</td>
<td title="Madhivanan">3</td>
<td title="PeterLarsson">4</td>
<td title="Sergejack">5</td>
</tr>
<tr>
<td title="JesseRoberge">6</td>
<td title="RobertPearl">7</td>
<td title="DavidBarbarin">8</td>
<td title="MichaelColes">9</td>
<td title="RiteshShah">10</td>
<td title="RuiCarvalho">11</td>
<td title="PinalDave">12</td>
</tr>
<tr>
<td title="LeszekGniadkowski">13</td>
<td title="LutzMueller">14</td>
<td title="NakulVachhrajani">15</td>
<td title="MikeLewis">16</td>
<td title="TejasShah">17</td>
<td title="FabienContaminard">18</td>
<td title="RamiReddy">19</td>
</tr>
<tr>
<td title="StefanGustafsson">20</td>
<td title="GogulaAryalingam">21</td>
<td title="AlejandroMesa">22</td>
<td title="SatyaJayanty">23</td>
<td title="ChintakChhapia">24</td>
<td title="SanjayBhatia">25</td>
<td title="NaomiNosonovsky">26</td>
</tr>
<tr>
<td title="SankarReddy">27</td>
<td title="NiladriBiswas">28</td>
<td title="MadhuNair">29</td>
<td title="VidhyaSagar">30</td>
<td title="KentWaldrop">31</td>
<td title=" "> </td>
<td title=" "> </td>
</tr>
</table>
</html>
希望还有方便 ，快捷的实现方式，请多交流讨论！
