求统计报表

Question

A 表　数据 
keyid　 CompereId Line 　　　Time　　　　　　　　　　 type 
296 003 x 2010-09-11 09:06:05 退出
294 004 x 2010-09-11 09:05:37 退出
295 001 x 2010-09-11 09:05:37 退出
292 001 x 2010-09-11 09:02:48 忙
293 001 x 2010-09-11 09:01:48 闲
290 004 x 2010-09-11 09:01:15 闲
291 001 x 2010-09-11 09:01:15 忙
289 001 x 2010-09-11 08:59:51 闲
288 001 x 2010-09-11 08:59:51 登陆
286 003 x 2010-09-11 08:52:58 登陆
287 004 x 2010-09-11 08:52:58 登陆
....
想的到结果
CompereId    登陆s   闲(次数) 闲(时长/s)　忙(次数) 　忙(时长/s)
001       　　346　　２　　　　144         2          202
003           787    0         0           0          0
004           759    1         262         0          0
....

Answer 1

create table tb(keyid int,CompereId nvarchar(10),Line nvarchar(10),Time datetime,type nvarchar(10))
insert into tb select 296,'003','x','2010-09-11 09:06:05','退出'
insert into tb select 294,'004','x','2010-09-11 09:05:37','退出'
insert into tb select 295,'001','x','2010-09-11 09:05:37','退出'
insert into tb select 292,'001','x','2010-09-11 09:02:48','忙'
insert into tb select 293,'001','x','2010-09-11 09:01:48','闲'
insert into tb select 290,'004','x','2010-09-11 09:01:15','闲'
insert into tb select 291,'001','x','2010-09-11 09:01:15','忙'
insert into tb select 289,'001','x','2010-09-11 08:59:51','闲'
insert into tb select 288,'001','x','2010-09-11 08:59:51','登陆'
insert into tb select 286,'003','x','2010-09-11 08:52:58','登陆'
insert into tb select 287,'004','x','2010-09-11 08:52:58','登陆'
go
with c1 as(
select row_number() over(partition by Compereid order by time,keyid) as rn,* from tb
),c2 as(
select rn,compereid,time,type,isnull((select time from c1 where Compereid=a.Compereid and rn=a.rn+1),time)nexttime from c1 a
)
select Compereid,
datediff(s,min(time),max(time)) as 登陆,
sum(case when type='闲' then 1 else 0 end)as [闲(次数)],
sum(case when type='闲' then datediff(s,time,nexttime) else 0 end)as [闲(时长/s)],
sum(case when type='忙' then 1 else 0 end)as [忙(次数)],
sum(case when type='忙' then datediff(s,time,nexttime) else 0 end)as [忙(时长/s)]
from c2 a group by Compereid
go
drop table tb
/*
Compereid  登陆          闲(次数)       闲(时长/s)     忙(次数)       忙(时长/s)
---------- ----------- ----------- ----------- ----------- -----------
001        346         2           144         2           202
003        787         0           0           0           0
004        759         1           262         0           0(3 行受影响)*/

Answer 2

数据是在sql2000上
 row_number()函数不认

Answer 3

create table tb(keyid int,CompereId nvarchar(10),Line nvarchar(10),Time datetime,type nvarchar(10))
insert into tb select 296,'003','x','2010-09-11 09:06:05',N'退出'
insert into tb select 294,'004','x','2010-09-11 09:05:37',N'退出'
insert into tb select 295,'001','x','2010-09-11 09:05:37',N'退出'
insert into tb select 292,'001','x','2010-09-11 09:02:48',N'忙'
insert into tb select 293,'001','x','2010-09-11 09:01:48',N'闲'
insert into tb select 290,'004','x','2010-09-11 09:01:15',N'闲'
insert into tb select 291,'001','x','2010-09-11 09:01:15',N'忙'
insert into tb select 289,'001','x','2010-09-11 08:59:51',N'闲'
insert into tb select 288,'001','x','2010-09-11 08:59:51',N'登陆'
insert into tb select 286,'003','x','2010-09-11 08:52:58',N'登陆'
insert into tb select 287,'004','x','2010-09-11 08:52:58',N'登陆'
go--sql server 2000 用临时表:
SELECT id = IDENTITY(INT, 1, 1), * INTO #tb FROM tb ORDER BY Compereid,time,keyid
--还是#1楼的SQL：
select Compereid,
datediff(s,min(time),max(time)) as 登陆,
sum(case when type=N'闲' then 1 else 0 end)as [闲(次数)],
sum(case when type=N'闲' then datediff(s,time,nexttime) else 0 end)as [闲(时长/s)],
sum(case when type=N'忙' then 1 else 0 end)as [忙(次数)],
sum(case when type=N'忙' then datediff(s,time,nexttime) else 0 end)as [忙(时长/s)]
from 
(
select id,compereid,time,type,isnull((select time from #tb where Compereid=a.Compereid and id=a.id+1),time)nexttime from #tb a
) b group by Compereid