27,579
社区成员
发帖
与我相关
我的任务
分享求教一条按小时进行分割的SQL语句。
OP_Act:
userid,act,time_stamp
1001,浏览,2008-6-1 11:58:00.000
1001,详细,2008-6-1 12:01:00.000
1001,订购,2008-6-1 12:02:00.000
1001,退出,2008-6-1 12:05:00.000
1001,登陆,2008-6-1 14:00:00.000
希望不使用游标得出以下格式的数据:
userid,act,time_stamp,timelong
1001,浏览,2008-6-1 11:58:00.000,120
1001,浏览,2008-6-1 12:00:00.000,60
1001,详细,2008-6-1 12:01:00.000,60
1001,订购,2008-6-1 12:02:00.000,180
1001,退出,2008-6-1 12:05:00.000,3300
1001,退出,2008-6-1 13:00:00.000,3300
--表示每个动作开始时间以及持续的秒数，跨小时的要以0分0秒为基准分割为二条记录,实现想不出办法了。
userid,act,time_stamp
1001,浏览,2008-6-1 11:58:00.000
1001,详细,2008-6-1 12:01:00.000
1001,订购,2008-6-1 12:02:00.000
1001,退出,2008-6-1 12:05:00.000
1001,登陆,2008-6-1 14:00:00.000
希望不使用游标得出以下格式的数据:
userid,act,time_stamp,timelong
1001,浏览,2008-6-1 11:58:00.000,120
1001,浏览,2008-6-1 12:00:00.000,60
1001,详细,2008-6-1 12:01:00.000,60
1001,订购,2008-6-1 12:02:00.000,180
1001,退出,2008-6-1 12:05:00.000,3300
1001,退出,2008-6-1 13:00:00.000,3300
--表示每个动作开始时间以及持续的秒数，跨小时的要以0分0秒为基准分割为二条记录,实现想不出办法了。
...全文
请发表友善的回复…
发表回复
齐天大圣etln 2008-08-22
- 打赏
- 举报
需要 加个字段 按字段分组做
zly1980 2008-08-08
- 打赏
- 举报
还不对哦。。。
如果是这样的数据:
OP_Act:
userid,act,time_stamp
1001,浏览,2008-6-1 11:58:00.000
1001,详细,2008-6-1 12:01:00.000
1001,订购,2008-6-1 12:02:00.000
1001,退出,2008-6-1 12:05:00.000
1001,登陆,2008-6-1 14:00:00.000
1001,退出,2008-6-2 14:00:00.000
如果是这样的数据:
OP_Act:
userid,act,time_stamp
1001,浏览,2008-6-1 11:58:00.000
1001,详细,2008-6-1 12:01:00.000
1001,订购,2008-6-1 12:02:00.000
1001,退出,2008-6-1 12:05:00.000
1001,登陆,2008-6-1 14:00:00.000
1001,退出,2008-6-2 14:00:00.000
zly1980 2008-08-07
- 打赏
- 举报
非常感谢！
-狙击手- 2008-08-07
- 打赏
- 举报
------------------------------------
-- Author:Flystone
-- Version:V1.001
-- Date:2008-08-07 23:15:54
------------------------------------
-- Test Data: OP_Act
If object_id('OP_Act') is not null
Drop table OP_Act
Go
Create table OP_Act(userid int,act nvarchar(2),time_stamp datetime)
Go
Insert into OP_Act
select 1001,'浏览','2008-6-1 11:58:00.000' union all
select 1001,'详细','2008-6-1 12:01:00.000' union all
select 1001,'订购','2008-6-1 12:02:00.000' union all
select 1001,'退出','2008-6-1 12:05:00.000' union all
select 1001,'登陆','2008-6-1 14:00:00.000'
Go
--Start
select * into # from op_act
union all
Select userid,act,dateadd(mi,60 - datepart(mi,time_stamp), time_stamp) from OP_Act a
where not exists(select 1 from op_act where datepart(hh,time_stamp) = datepart(hh,a.time_stamp)
and time_stamp > a.time_stamp)
order by time_stamp
select userid,act,time_stamp,
timelong = datediff(ss,time_stamp,isnull((select top 1 time_stamp from # where time_stamp > a.time_stamp),time_stamp))
from # a
drop table #
--Result:
/*
userid act time_stamp timelong
----------- ---- ------------------------------------------------------ -----------
1001 浏览 2008-06-01 11:58:00.000 120
1001 浏览 2008-06-01 12:00:00.000 60
1001 详细 2008-06-01 12:01:00.000 60
1001 订购 2008-06-01 12:02:00.000 180
1001 退出 2008-06-01 12:05:00.000 3300
1001 退出 2008-06-01 13:00:00.000 3600
1001 登陆 2008-06-01 14:00:00.000 3600
1001 登陆 2008-06-01 15:00:00.000 0
（所影响的行数为 8 行）
*/
--End
-狙击手- 2008-08-07
- 打赏
- 举报
[Quote=引用 2 楼 zly1980 的回复:]
：（没时长哦。
[/Quote]
哦，那再来
：（没时长哦。
[/Quote]
哦，那再来
zly1980 2008-08-07
- 打赏
- 举报
：（没时长哦。
-狙击手- 2008-08-07
- 打赏
- 举报
------------------------------------
-- Author:Flystone
-- Version:V1.001
-- Date:2008-08-07 23:15:54
------------------------------------
-- Test Data: OP_Act
If object_id('OP_Act') is not null
Drop table OP_Act
Go
Create table OP_Act(userid int,act nvarchar(2),time_stamp datetime)
Go
Insert into OP_Act
select 1001,'浏览','2008-6-1 11:58:00.000' union all
select 1001,'详细','2008-6-1 12:01:00.000' union all
select 1001,'订购','2008-6-1 12:02:00.000' union all
select 1001,'退出','2008-6-1 12:05:00.000' union all
select 1001,'登陆','2008-6-1 14:00:00.000'
Go
--Start
select * from op_act
union all
Select userid,act,dateadd(mi,60 - datepart(mi,time_stamp), time_stamp) from OP_Act a
where not exists(select 1 from op_act where datepart(hh,time_stamp) = datepart(hh,a.time_stamp)
and time_stamp > a.time_stamp)
order by time_stamp
--Result:
/*
userid act time_stamp
----------- ---- ------------------------------------------------------
1001 浏览 2008-06-01 11:58:00.000
1001 浏览 2008-06-01 12:00:00.000
1001 详细 2008-06-01 12:01:00.000
1001 订购 2008-06-01 12:02:00.000
1001 退出 2008-06-01 12:05:00.000
1001 退出 2008-06-01 13:00:00.000
1001 登陆 2008-06-01 14:00:00.000
1001 登陆 2008-06-01 15:00:00.000
（所影响的行数为 8 行）
*/
--End
