问题如下:
有一张表 table1,内容大概如下:ID startdate enddate1 2010-03-21 19:00:00.000 2010-03-22 02:00:00.000
2 2010-03-22 16:00:00.000 2010-03-23 01:30:00.000
3 2010-03-22 16:00:00.000 2010-03-23 10:00:00.000
4 2010-03-24 18:00:00.000 2010-03-24 22:30:00.000
5 2010-03-25 06:00:00.000 2010-03-25 09:00:00.000
6 2010-03-25 15:00:00.000 2010-03-25 23:00:00.000利用Sql语句计算出startdate和enddate之间包含自然日的个数,所有的记录要累加。
重复的日期不算,如2010-03-22出现3次,只能算作一次。
-------------------------
要求:
1、不用临时表。
2、不用游标。
3、不用视图。
-------------------------
有一张表 table1,内容大概如下:ID startdate enddate1 2010-03-21 19:00:00.000 2010-03-22 02:00:00.000
2 2010-03-22 16:00:00.000 2010-03-23 01:30:00.000
3 2010-03-22 16:00:00.000 2010-03-23 10:00:00.000
4 2010-03-24 18:00:00.000 2010-03-24 22:30:00.000
5 2010-03-25 06:00:00.000 2010-03-25 09:00:00.000
6 2010-03-25 15:00:00.000 2010-03-25 23:00:00.000利用Sql语句计算出startdate和enddate之间包含自然日的个数,所有的记录要累加。
重复的日期不算,如2010-03-22出现3次,只能算作一次。
-------------------------
要求:
1、不用临时表。
2、不用游标。
3、不用视图。
-------------------------
insert into T
select 1, '2010-03-21 19:00:00.000', '2010-03-22 02:00:00.000'
union all select 2 ,'2010-03-22 16:00:00.000', '2010-03-23 01:30:00.000'
union all select 3 ,'2010-03-22 16:00:00.000', '2010-03-23 10:00:00.000'
union all select 4 ,'2010-03-24 18:00:00.000', '2010-03-24 22:30:00.000'
union all select 5 ,'2010-03-25 06:00:00.000', '2010-03-25 09:00:00.000'
union all select 6 ,'2010-03-25 15:00:00.000', '2010-03-25 23:00:00.000'GOselect distinct convert(char(10),dateadd(day, A.number, B.startdate) ,120) as [date]
from master.dbo.spt_values as A, T as B
where A.number between 0 and datediff(day,B.startdate,B.enddate)
order by [date]
Go
/*
2010-03-21
2010-03-22
2010-03-23
2010-03-24
2010-03-25
*/drop table T