再问按时间查询问题！

xyuncn 2003-07-23 08:14:59

表的形式为
id starttm
1 2003-07-15 11:27:53
2 2003-07-15 11:27:53
3 2003-07-15 11:27:53
4 2003-07-15 11:27:54
5 2003-07-15 11:27:55
6 2003-07-15 11:27:56
7 2003-07-15 11:27:56
8 2003-07-15 11:27:57
...........................

需要按时间进行抽样统计,譬如抽样时间间隔为3秒,统计结果为
tm count1
2003-07-15 11:27:53 3
2003-07-15 11:27:56 2
.............................

该sql语句实现为
select starttm tm, count(*) countl from table1
where datediff(ss, (select min(startm) from table1), starttm) % 3 = 0
group by starttm

如果在抽样时刻没有记录,如何让他显示为0？
即若在2003-07-15 11:27:59时刻没有记录,统计结果为:

tm count1
2003-07-15 11:27:53 3
2003-07-15 11:27:56 2
2003-07-15 11:27:59 0
.............................

thanks

...全文

13 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

xyuncn 2003-07-23

打赏
举报

谢谢大家:)

erigido 2003-07-23

打赏
举报

拙见:
declare @d datetime,@s datetime,@e datetime,@b char(1),@i int
set @e = '2003-07-15 11:28:49'
set @d = '2003-07-15 11:27:53'
drop table #t
CREATE TABLE #t(a int IDENTITY(1,1),
x datetime PRIMARY KEY,
c int)
while @d<@e
begin
if((select count(*) from table1 where starttm = @d)<>0)
set @b = '1'
else set @b = '0'
set @i = (select case @b when '1' then count(*) else 0 end as c
from table1
where starttm = @d
)
insert into #t (x,c)
values(@d ,@i)
set @d = dateadd(ss,3,@d)
end
select * from #t

zjcxc 元老 2003-07-23

打赏
举报

那你告诉我,原表中那里有
2003-07-15 11:27:59

这一条,你是根据什么原则来生成它的?

pengdali 2003-07-23

打赏
举报

declare @min datetime,@max datetime
select @min=min(startm),@max=max(startm) from table1

select tem1.时间,isnull(tem2.countl,0) 数量 from
(select dateadd(ss,c*3,@min) 时间 from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b 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) t1,
(select 0 as b 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) t2,
(select 0 as b 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) t3,
(select 0 as b 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) t4
) t5 where dateadd(ss,c*3,@min)<@max)
tem1 left join
(select starttm tm, count(*) countl from table1
where datediff(ss, @min, starttm) % 3 = 0
group by starttm) tem2 on tem1.时间=tem2.tm

pengdali 2003-07-23

打赏
举报

declare @min datetime,@max datetime
select @min=min(startm),@max=max(startm) from table1

select tem1.时间,isnull(tem2.countl,0) from
(select dateadd(ss,c*3,@min) 时间 from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 c from
(select 0 as b 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) t1,
(select 0 as b 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) t2,
(select 0 as b 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) t3,
(select 0 as b 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) t4
) t5 where dateadd(ss,c*3,@min)<@max)
tem1 left join
(select starttm tm, count(*) countl from table1
where datediff(ss, (select min(startm) from table1), starttm) % 3 = 0
group by starttm) tem2 on tem1.时间=tem2.tm

erigido 2003-07-23