比如有表 user_log
有userid和operate_time两列,分别表示用户的ID和操作时间
我要从中找出那些两次操作时间间隔大于30秒的那些用户
userid operate_time
a 2011-1-4 10:00:00
b 2011-1-4 10:00:00
b 2011-1-4 10:00:05
a 2011-1-4 10:00:31
比如上面的示例中应该查出用户 a 出来,因为的他的两次操作时间2011-1-4 10:00:00和2011-1-4 10:00:31间隔为31秒,大于30秒,而用户b的间隔只有5秒
谢谢指点啦
------解决方案--------------------
- SQL code
;with cte as(select *,row_number() over (partition by userid order by operate_time) rid from tb)select * from cte a left join cte b on (a.rid=b.rid-1 and datediff(seconde,a.operate_time,b.operate_time)>30)--没环境，LZ试下，思路差不多，细节自己可以改下
------解决方案--------------------
- SQL code
use Tempdbgo--> --> if not object_id(N'user_log') is null drop table user_logGoCreate table user_log([userid] nvarchar(2),[operate_time] Datetime)Insert user_logselect N'a','2011-1-4 10:00:00' union allselect N'b','2011-1-4 10:00:00' union allselect N'b','2011-1-4 10:00:05' union allselect N'a','2011-1-4 10:00:31'GoSelect * from user_log AS aWHERE DATEADD(s,5,[operate_time])<(SELECT MIN([operate_time]) FROM user_log WHERE [userid]=a.[userid] AND [operate_time]>a.[operate_time])/*userid operate_timea 2011-01-04 10:00:00.000*/