sql – 选择将日期相同的列分组到不同的列中

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

但结果没有按日期分组.

什么是正确的方法？

解决方法

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)

An SQLfiddle to test with.

编辑：未经测试的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)

Another SQLfiddle.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!