1、建立序数表:
select top 10000 identity(int,0,2) id into 序数表 from sysobjects a,sysobjects b,sysobjects2、
declare @start datetime,@end datetime, @cnt intselect @start=min([starttm]),@end=max([endtm]) from mytableselect *,(select sum(1) from mytable where [starttm]<=时间 and 时间<= [endtm]) 合计 from (
select dateadd(ss,id,@start) 时间 from 序数表 where dateadd(ss,id,@start)<=@end ) tem
select top 10000 identity(int,0,2) id into 序数表 from sysobjects a,sysobjects b,sysobjects2、
declare @start datetime,@end datetime, @cnt intselect @start=min([starttm]),@end=max([endtm]) from mytableselect *,(select sum(1) from mytable where [starttm]<=时间 and 时间<= [endtm]) 合计 from (
select dateadd(ss,id,@start) 时间 from 序数表 where dateadd(ss,id,@start)<=@end ) tem
declare @cnt int
set @start = (select min([starttm]) from mytable)
set @end = (select max([endtm,]) from mytable)
drop table #result
create table #result
(
starttm datetime,
cnt int
)
while(@start < @end)
begin
insert #result values (@start, 0)
set @start = dateadd(ss,2,@start)
end
update #result
set cnt = (select count(*) from mytable
where A.starttm between [starttm] and [endtm])
from #result A
select * from #result
declare @cnt int
set @start = (select min([starttm]) from mytable)
set @end = (select max([endtm,]) from mytable)
drop table #result
create table #result
(
starttm datetime,
cnt int
)
while(@start < @end)
begin
insert #result values (@start, 0)
set @start = dateadd(ss,2,@start)
end
update #result
set cnt = (select count(*) from mytable
where A.starttm between [starttm] and [endtm])
from #result A
select * from #result
select dateadd(ss,id,@start),* from tableName where dateadd(ss,id,@start)<=@end) temalter table tableName drop column id
insert into test
select 1,'2003-07-28 10:37:35.000','2003-07-28 10:37:39.000'
union all
select 2,'2003-07-28 10:37:36.000','2003-07-28 10:37:45.000'
union all
select 3,'2003-07-28 10:37:36.000','2003-07-28 10:38:55.000'
union all
select 4,'2003-07-28 10:37:37.000','2003-07-28 10:39:25.000'
union all
select 5,'2003-07-28 10:37:37.000','2003-07-28 10:37:39.000'
union all
select 6,'2003-07-28 10:37:37.000','2003-07-28 10:37:45.000'
alter table test add md int identity(0,2)
go
declare @start datetime,@end datetime
declare @cnt int
set @start = (select min([starttm]) from test)
set @end = (select max([endtm]) from test)
select starttm,(select sum(1) from test where @start between [starttm] and [endtm]) cnt from (
select dateadd(ss,md,@start) as starttm from test where dateadd(ss,md,@start)<=@end) tem
go
alter table test drop column md
go
go
declare @start datetime,@end datetime
declare @cnt int
set @start = (select min([starttm]) from test)
set @end = (select max([endtm]) from test)
select *,(select sum(1) from test where 时间 between [starttm] and [endtm]) cnt from (
select dateadd(ss,md,@start) 时间 from test where dateadd(ss,md,@start)<=@end) tem
go
alter table test drop column md
go
你的方法统计出来的结果有点不对
select top 10000 identity(int,0,10000) id into 序数表 from sysobjects a,sysobjects b,sysobjects2、
declare @start datetime,@end datetime, @cnt intselect @start=min([starttm]),@end=max([endtm]) from mytable
select (ss,aa.id,bb.startTime),(select count(*) from mytable where [starttm] <= @(ss,aa.id,bb.startTime) and @start <= [endtm] )
from
序数表 aa left join (select @start as StartTime)bb on dateadd(ss,aa.id,bb.startTime)<=@end
where bb.starttime is not null