# 如何生成一个简易年(月)历?

playyuer 2004-03-25 10:13:45

[难题]如何统计出一个月中没有出现的天的次数？
http://expert.csdn.net/Expert/topic/2884/2884968.xml?temp=.509762

/*
select * into dates
from
(
select getdate() as d
union all
select getdate() + 1
union all
select getdate() + 1
) T
*/

from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31
) N
,
(select min(d) as d
from dates
group by datediff(month,0,d)) M

and not exists (select 1
from Dates

declare @ datetime
set @ = getdate() --'2004-03-25'
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) M
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
) d
order by [Date]

...全文
51 51 打赏 收藏 举报

51 条回复

lovvver 2004-04-04

• 打赏
• 举报

welyngj 2004-04-04
UP
• 打赏
• 举报

j9988 2004-04-04

• 打赏
• 举报

playyuer 2004-04-04

• 打赏
• 举报

zheninchangjiang 2004-04-04
up
• 打赏
• 举报

playyuer 2004-04-04
up
• 打赏
• 举报

zhangzs8896 2004-04-04
study
• 打赏
• 举报

bhdxia 2004-04-04

• 打赏
• 举报

vileboy 2004-04-04

• 打赏
• 举报

shuichangliu 2004-04-04

• 打赏
• 举报

huangder 2004-04-04

• 打赏
• 举报

「已注销」 2004-04-04

• 打赏
• 举报

31737951 2004-04-04

• 打赏
• 举报

j9988 2004-04-04

• 打赏
• 举报

playyuer 2004-04-04
--2004-04-04 修订
--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)
--注意 datename 的值会因 SQL Server 语言版本或日期格式有所差异!
--本测试环境为: SQL Server 2000 简体中文版 + Windows 简体中文版

declare @ datetime
set @ = '1995-02-25 11:00:50' -- 1995-01-01 正好是个星期日

select @ as 日期

,datediff
(
week
end

end
)
+ 1 as MyWeekOfYear

,datediff
(week
end
end

)
+1 as MyWeekOfMonth

into D

from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) M
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
) d
order by [Date]

select month(min(date)) as 月份
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期日' and datediff(month,min(a.date),date) = 0) as 星期日
, (select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期一' and datediff(month,min(a.date),date) = 0) as 星期一
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期二' and datediff(month,min(a.date),date) = 0) as 星期二
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期三' and datediff(month,min(a.date),date) = 0) as 星期三
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期四' and datediff(month,min(a.date),date) = 0) as 星期四
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期五' and datediff(month,min(a.date),date) = 0) as 星期五
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期六' and datediff(month,min(a.date),date) = 0) as 星期六

from d a
group by datediff(month,0,date),datepart(week,date)
• 打赏
• 举报

ygghost 2004-03-29

• 打赏
• 举报

DreamManor 2004-03-29

http://expert.csdn.net/Expert/topic/2751/2751870.xml?temp=.7578546
• 打赏
• 举报

pbsql 2004-03-29

• 打赏
• 举报

shuichangliu 2004-03-29

• 打赏
• 举报

xiangdafang 2004-03-29

• 打赏
• 举报

3.3w+

MS-SQL Server相关内容讨论专区

2004-03-25 10:13