我有一个登录表,如下所示：
Created | Action 20/01/2016 08:00:00 AM login 20/01/2016 10:05:10 AM logout 20/01/2016 12:00:00 PM login 20/01/2016 04:12:22 PM logout 21/01/2016 08:00:50 AM login 21/01/2016 09:44:42 AM login 21/01/2016 10:44:42 AM login 21/01/2016 04:00:42 PM logout
我需要选择一个如下所示的结果集：
Created | Login | logout 20/01/2016 08:00:00 AM 04:12:22 PM 21/01/2016 08:00:50 PM 04:00:42 PM
这是我尝试过的：
SELECT
CONVERT(VARCHAR(10),li.Created,10) [Date],CONVERT(VARCHAR(8),MAX(li.Created),8) [Login],MAX(lo.Created),8) [logout]
FROM Logins li
LEFT JOIN Logins lo ON lo.[Action] = 'logout'
GROUP BY li.Created
但结果没有按日期分组.
什么是正确的方法？
解决方法
与Rahul的答案类似,但您可以自行加入以获得首次登录/上次登出,并按日期分组以获得您要求的结果;
SELECT CONVERT(DATE,li.created) [Date],CONVERT(TIME,MIN(li.created)) [Login],MAX(lo.created)) [logout] FROM Logins li JOIN Logins lo ON CONVERT(DATE,li.created) = CONVERT(DATE,lo.created) AND li.action = 'login' AND lo.action = 'logout' GROUP BY CONVERT(DATE,li.created)
编辑：未经测试的sql Server 2005,您可能需要设置varchars的长度;
SELECT CONVERT(VARCHAR,li.created,110) [Date],CONVERT(VARCHAR,MAX(li.created),MAX(lo.created),8) [logout] FROM Logins li JOIN Logins lo ON CONVERT(VARCHAR,110) = CONVERT(VARCHAR,lo.created,110) AND li.action = 'login' AND lo.action = 'logout' GROUP BY CONVERT(VARCHAR,110)
