我现在的数据是这样的,userid表示用户登录系统的代码,time表示登录登出系统的时间,status,1表示登录,2表示登出;
userid time status
user001 2011-06-10 14:57:24.000 1
user001 2011-06-10 15:03:41.000 2
user001 2011-06-10 16:50:00.000 1
user001 2011-06-10 17:03:40.000 2
user001 2011-06-10 17:20:40.000 1
user002 2011-06-10 10:01:20.000 1
user002 2011-06-10 10:20:24.000 1
user002 2011-06-10 10:40:40.000 2
user002 2011-06-10 13:50:00.000 1
user002 2011-06-10 15:03:40.000 2
我想要的结果是:
用户代码 登录时间区间
user001 2011-06-10 14:57:24.000--2011-06-10 15:03:41.000 6 minutes
user001 2011-06-10 16:50:00.000--2011-06-10 17:03:40.000 13 minutes
user001 2011-06-10 17:20:40.000 0 minutes
user002 2011-06-10 10:01:20.000--2011-06-10 10:40:40.000 39 minutes
user002 2011-06-10 10:20:24.000-- 0 minutes
user002 2011-06-10 13:50:00.000--2011-06-10 15:03:40.000 73minutes 我的目的是统计每个用户登录系统的时间的汇总。
userid time status
user001 2011-06-10 14:57:24.000 1
user001 2011-06-10 15:03:41.000 2
user001 2011-06-10 16:50:00.000 1
user001 2011-06-10 17:03:40.000 2
user001 2011-06-10 17:20:40.000 1
user002 2011-06-10 10:01:20.000 1
user002 2011-06-10 10:20:24.000 1
user002 2011-06-10 10:40:40.000 2
user002 2011-06-10 13:50:00.000 1
user002 2011-06-10 15:03:40.000 2
我想要的结果是:
用户代码 登录时间区间
user001 2011-06-10 14:57:24.000--2011-06-10 15:03:41.000 6 minutes
user001 2011-06-10 16:50:00.000--2011-06-10 17:03:40.000 13 minutes
user001 2011-06-10 17:20:40.000 0 minutes
user002 2011-06-10 10:01:20.000--2011-06-10 10:40:40.000 39 minutes
user002 2011-06-10 10:20:24.000-- 0 minutes
user002 2011-06-10 13:50:00.000--2011-06-10 15:03:40.000 73minutes 我的目的是统计每个用户登录系统的时间的汇总。
解决方案 »
- 请教各位大侠一个查询语句的问题~ 谢谢
- FETCH NEXT FROM MYCUR INTO 到表变量中可以吗
- 如何导出2005里的数据
- 创建登录用户的语法 sp_addlogin 中,参数应该不应该加上 单引号 定界符呀,我试了下,发现加不加都是一样的效果,这是为什么?
- 如何计算每个文章分类下的 文章总数
- 在sql中如何定义数组?
- 请大家看下!!!关于自动增加的问题??
- 谁需要SQL SERVER 7.0的各种技术资料!!!
- 如何得到当前局域网中正在运行的所有的数据库(currently active server)的列表?
- 用raw类型定义以unicode码保存的信息.怎样正确显示?
- 删除触发器将操作用户保存到另一个表的字段里
- 请教SQL语句
from tb a left join ,tb b
on a.userid=b.userid and b.status=2
and b.time=(select top 1 time from tb c
where c.userid=a.userid and c.status=2
and c.time>a.time order by c.time)
where a.status=1
declare @t table(iId int identity(1,1),[userid] varchar(20), [time] datetime, [status] int)
Insert @t([userid], [time], [status])
select 'user001', '2011-06-10 14:57:24.000', 1 Union All
select 'user001', '2011-06-10 15:03:41.000', 2 Union All
select 'user001', '2011-06-10 16:50:00.000', 1 Union All
select 'user001', '2011-06-10 17:03:40.000', 2 Union All
select 'user001', '2011-06-10 17:20:40.000', 1 Union All
select 'user002', '2011-06-10 10:01:20.000', 1 Union All
select 'user002', '2011-06-10 10:20:24.000', 1 Union All
select 'user002', '2011-06-10 10:40:40.000', 2 Union All
select 'user002', '2011-06-10 13:50:00.000', 1 Union All
select 'user002', '2011-06-10 15:03:40.000', 2
select *, ISNULL(DATEDIFF(MINUTE, [time], EndDate), 0) iMINUTE from (
select *, (select top 1 [time] from @t where A.userid=userid and [status] = 2 and A.iId<=iId order by iId) EndDate from @t A where [status] = 1
) A
iId userid time status EndDate iMINUTE
----------- -------------------- ----------------------- ----------- ----------------------- -----------
1 user001 2011-06-10 14:57:24.000 1 2011-06-10 15:03:41.000 6
3 user001 2011-06-10 16:50:00.000 1 2011-06-10 17:03:40.000 13
5 user001 2011-06-10 17:20:40.000 1 NULL 0
6 user002 2011-06-10 10:01:20.000 1 2011-06-10 10:40:40.000 39
7 user002 2011-06-10 10:20:24.000 1 2011-06-10 10:40:40.000 20
9 user002 2011-06-10 13:50:00.000 1 2011-06-10 15:03:40.000 73(6 行受影响)
这个有那么点漏洞,就是有进无出的不能判断出来
a.userid,a.time,b.time,isnull(datediff(mi,,a.time,b.time),0)
from
tb a left join tb b
on
a.userid=b.userid
and
b.status=2
and
b.time=(select top 1 time from tb c where c.userid=a.userid and c.status=2 and c.time>a.time order by c.time)
where
a.status=1
from tb a left join ,tb b
on a.userid=b.userid and b.status=2
and b.time=(select top 1 time from tb c
where c.userid=a.userid and c.status=2
and c.time>a.time order by c.time)
where a.status=1