SQL 打印某一年日历

     显示某一年的日历，效果图如下：

一年中的某一天必须对应正确的月份和正确星期。

SQL代码：

create function Func_PrintCalendar(
@year int
)
returns 
@Cal 
table
(
Month varchar(2),
Sunday varchar(2),
Monday varchar(2),
Tuesday varchar(2),
Wednesday varchar(2),
Thursday varchar(2),
Friday varchar(2),
Saturday varchar(2)
) 
as
begin

--声明日历表
declare @Calendar table
(
Month varchar(2),             --月份
Weekday varchar(10),          --星期几
DayOfMonth varchar(2),        --第几天（某一月）
Week int                      --第几周（一年中）
)

--插入数据
declare @dt date=cast(@year as varchar(4))+'-01-01'
while(@dt<=dateadd(day,-1,(cast(@year+1 as varchar(4))+'-01-01')))
begin
insert @Calendar values(cast(MONTH(@dt) as varchar),DATENAME(WEEKDAY,@dt),cast(DAY(@dt) as varchar),DATEPART(WEEK,@dt))
set @dt=DATEADD(day,1,@dt)
end

--相同的月份，只显示第一条；星期对应的null改为''
insert @Cal
select case when (ROW_NUMBER() over(partition by [Month] order by [Week]))=1 then [Month] else '' end as [Month],
ISNULL(Sunday,'') as 'Sunday',
ISNULL(Monday,'') as 'Monday',
ISNULL(Tuesday,'') as 'Tuesday',
ISNULL(Wednesday,'') as 'Wednesday',
ISNULL(Thursday,'') as 'Thursday',
ISNULL(Friday,'') as 'Friday',
ISNULL(Saturday,'') as 'Saturday'
from
(
--行专列处理，其实也可以用pivot()函数
select [Month],[Week],
max(case when Weekday='星期日' then DayOfMonth end) as 'Sunday',
max(case when Weekday='星期一' then DayOfMonth end) as 'Monday',
max(case when Weekday='星期二' then DayOfMonth end) as 'Tuesday',
max(case when Weekday='星期三' then DayOfMonth end) as 'Wednesday',
max(case when Weekday='星期四' then DayOfMonth end) as 'Thursday',
max(case when Weekday='星期五' then DayOfMonth end) as 'Friday',
max(case when Weekday='星期六' then DayOfMonth end) as 'Saturday'
from @Calendar 
group by [Month],[Week]
) as Calendar Order by [Week]
 return
end
 go

自定义一个函数，每次使用时，就可以调用它。

 select * from Func_PrintCalendar(2015)