我有一个记录登录/注销的表格。架构和示例数据:http://sqlfiddle.com/#!2/e1b35/1
我需要创建存储过程,该过程将打印用户的(注销登录)时间和日期(在日期为用户使用应用程序的持续时间)。在列中,类型 I 表示登录和 O 注销。
输入参数:@username、@date。输出:时间。
您需要标识彼此相关的所有登录名组。 我这样做的方法是找到与登录相关的注销。 您正在处理日志数据,因此如果有多个登录没有注销,请不要感到惊讶。
select l.*,
(select min(l2.time) from logs l2 where l2.username = l.username and l2.type = 'O' and l2.time > l.time
) as logoutTime
from logs l
where l.type = 'I'
现在,您可以使用用户名和 LogoutTime 作为一对进行聚合,以获得所需的内容:
select username, logoutTime, min(time) as StartTime, logouttime as EndTime,
datediff(s, min(time), logoutTime) as TimeInSeconds
from (select l.*,
(select min(l2.time) from logs l2 where l2.username = l.username and l2.type = 'O' and l2.time > l.time
) as logoutTime
from logs l
where l.type = 'I'
) l
group by username, logoutTime
请注意,您将 SQL Server 指定为标记,但 SQL Fiddle 适用于 MySQL;日期函数往往因数据库而异。
而且,如果您使用的是SQL Server 2012,则有一种更简单的方法。 如果是这种情况,您应该指定。
这是Gordon Linoff的解决方案,经过修改,它不包括最外层的聚合,并使用更清晰的别名,IMO
select username,time_in,time_out_min,
datediff(s, time_in, time_out_min) as TimeInSeconds
from (
select i.username, i.time as time_in,
(select min(o.time) as min_time_out
from logs o
where o.username = i.username and
o.type = 'O' and
o.time > i.time
) as time_out_min
from logs i
where i.type = 'I'
) d;