select top 365 identity(int,0,1) id into #a from sysobjects a,sysobjects
--如果要月:
declare @a datetime
set @a='2003-5-1'select dateadd(day,id,@a) 时间,(select count(*) from 预订表 where reserve_time=dateadd(day,#a.id,@a)) 和 from #a where datediff(month,dateadd(day,id,@a),@a)=0--如果要年:
--declare @a datetime
set @a='2003-1-1'select dateadd(day,id,@a) 时间,(select count(*) from 预订表 where reserve_time=dateadd(day,#a.id,@a)) 和 from #a where year(dateadd(day,id,@a))=year(@a)go
drop table #a
--如果要月:
declare @a datetime
set @a='2003-5-1'select dateadd(day,id,@a) 时间,(select count(*) from 预订表 where reserve_time=dateadd(day,#a.id,@a)) 和 from #a where datediff(month,dateadd(day,id,@a),@a)=0--如果要年:
--declare @a datetime
set @a='2003-1-1'select dateadd(day,id,@a) 时间,(select count(*) from 预订表 where reserve_time=dateadd(day,#a.id,@a)) 和 from #a where year(dateadd(day,id,@a))=year(@a)go
drop table #a
Select (select sum(1) from sysobjects where id <= a.id)-1 as id into #tmp from sysobjects a
order by id
2: 再来统计
Select dateadd(day,a.id,'2003-09-01') as date,
(select count(reserve_time) from 预订表 where datediff(day,reserve_time, dateadd(day,a.id,'2003-09-01')) = 0) as count
from #tmp a
group by reserve_time
Select dateadd(day,a.id,'2003-09-01') as date,
(select count(reserve_time) from 预订表 where datediff(day,reserve_time, dateadd(day,a.id,'2003-09-01')) = 0) as count
from (Select (select sum(1) from sysobjects where id <= a.id)-1 as id into #tmp from sysobjects a
order by id) a
Select dateadd(day,a.id,'2003-09-01') as date,
(select count(reserve_time) from 预订表 where datediff(day,reserve_time, dateadd(day,a.id,'2003-09-01')) = 0) as count
from (Select (select sum(1) from sysobjects where id <= b.id)-1 as id from sysobjects b
order by id) a