34,575
社区成员
发帖
与我相关
我的任务
分享
create table #meal(id varchar(10),d1 char(10), t1 char(8))
go
insert #meal select 'a21','2010-03-21','09:30:12'
insert #meal select 'a22','2010-03-21','09:31:22'
insert #meal select 'a23','2010-03-21','09:32:34'
insert #meal select 'a21','2010-03-21','10:01:42'
insert #meal select 'a24','2010-03-21','10:01:56'
insert #meal select 'a21','2010-03-21','10:20:25'
insert #meal select 'a22','2010-03-21','10:20:34'
go
--'2010-03-21'到'09:30:12'
declare @date varchar(10),@time varchar(8)
select @date = '2010-03-21',@time='09:30:12'
select id,count(1)
from #meal
where convert(datetime,d1+' '+t1) between convert(datetime,@date+' '+@time) and DATEADD (mi , 60, convert(datetime,@date+' '+@time) )
group by id
drop table #meal
--'2010-03-21'-'09:30:12'一个小时内数据
---------------------------------------
a21 3
a22 2
a23 1
a24 1
--'2010-03-21'-'10:00:00'一个小时内数据
a21 2
a22 1
a24 1
create table #meal(id varchar(10),d1 char(10), t1 char(8))
insert #meal select 'a21','2010-03-21','09:30:12'
insert #meal select 'a22','2010-03-21','09:31:22'
insert #meal select 'a23','2010-03-21','09:32:34'
insert #meal select 'a21','2010-03-21','10:01:42'
insert #meal select 'a24','2010-03-21','10:01:56'
insert #meal select 'a21','2010-03-21','10:20:25'
insert #meal select 'a22','2010-03-21','10:20:34'
declare @begindt datetime
,@i int --区间
set @begindt='2010-03-21 09:30:00'
set @i=1
select id,count(*) from #meal
where datediff(ss,@begindt,d1+' '+t1)<=@i*3600
group by id
id
---------- -----------
a21 3
a22 2
a23 1
a24 1
(4 行受影响)
-- 上面错误
create table #meal(id varchar(10),d1 char(10), t1 char(8))
go
insert #meal select 'a21','2010-03-21','09:30:12'
insert #meal select 'a22','2010-03-21','09:31:22'
insert #meal select 'a23','2010-03-21','09:32:34'
insert #meal select 'a21','2010-03-21','10:01:42'
insert #meal select 'a24','2010-03-21','10:01:56'
insert #meal select 'a21','2010-03-21','10:20:25'
insert #meal select 'a22','2010-03-21','10:20:34'
-- 为了让问题更清楚,添加几个数据
insert #meal select 'a21','2010-03-21','12:20:34'
insert #meal select 'a21','2010-03-21','12:23:34'
insert #meal select 'a22','2010-03-22','10:20:34'
go
select t1.id,count(*) cnt,min(t1.d1+' '+t1.t1) [start]
from #meal t1 inner join
(select id,'2010-03-21 00:'+right(min(t1),5) [diff] from #meal group by id) t2
on t1.id=t2.id
group by t1.id,datediff(ss,t2.diff,t1.d1+' '+t1.t1)/3600
/*
a21 3 2010-03-21 09:30:12
a22 2 2010-03-21 09:31:22
a23 1 2010-03-21 09:32:34
a24 1 2010-03-21 10:01:56
-----------------------------------
a21 2 2010-03-21 12:20:34
a22 1 2010-03-22 10:20:34
*/
select t1.id,max(t1.cnt) from
(select t1.id, t1.d1+' '+t1.t1 [start], COUNT(*) cnt
from #meal t1 join #meal t2
on t1.id=t2.id and datediff(ss,t1.d1+' '+t1.t1,t2.d1+' '+t2.t1) between 0 and 3600
group by t1.id,t1.d1+' '+t1.t1) t1
inner join
(select id,'2010-03-21 00:'+right(min(t1),5) [diff] from #meal group by id) t2
on t1.id=t2.id
group by t1.id,datediff(ss,[diff],[start])/3600
/*
a21 3
a22 2
a23 1
a24 1
*/
create table #meal(id varchar(10),d1 char(10), t1 char(8))
go
insert #meal select 'a21','2010-03-21','09:30:12'
insert #meal select 'a22','2010-03-21','09:31:22'
insert #meal select 'a23','2010-03-21','09:32:34'
insert #meal select 'a21','2010-03-21','10:01:42'
insert #meal select 'a24','2010-03-21','10:01:56'
insert #meal select 'a21','2010-03-21','10:20:25'
insert #meal select 'a22','2010-03-21','10:20:34'
go
-- 很难了解 lz 要什么,不知道这个是否合适?
declare cur cursor for
select id,convert(datetime,d1+' '+t1) from #meal order by 2;
open cur;
declare @id varchar(10),@time datetime;
fetch next from cur into @id,@time;
while @@FETCH_STATUS=0
begin
select id,COUNT(id) cnt,
@time [start_time], DATEADD(SS,3600,@time) [end_time]
from #meal
where DATEDIFF(SS,@time,d1+' '+t1) between 0 and 3600
group by id;
fetch next from cur into @id,@time;
end
close cur;
deallocate cur;