詢問 MS SQL查詢相關問題

ms sql sql

ioveasdkre 2022-03-18 16:02:45 ‧ 841 瀏覽

分享至

我現在有一張查詢登入表

LoginLog
ID, name, LoginTime, LogoutTimt

1, a, 2022/03/01 09:00:00, 2022/03/01 15:00:00
2, a, 2022/03/02 02:00:00, null
3, a, 2022/03/03 10:00:00, 2022/03/18 11:00:00
4, a, 2022/03/04 07:00:00, null
5, b, 2022/03/04 07:00:00, 08:00:00
6, a, 2022/03/05 020:00:00, null
7. b, 2022/03/06 08:00:00, null
8, a, 2022/03/06 10:00:00, 2022/03/18 11:00:00
9. b, 2022/03/04 16:00:00, null
.........

查詢結果希望如下：
如果有登出紀錄，就使用登出紀錄。
如果沒有登出紀錄，就用下一次登入時間，減去 1秒

;WITH LoginOutTable AS (
SELECT *
, ROW_NUMBER() OVER (PARTITION BY l.Name ORDER BY l.Id) [RowNo]
FROM LoginLog l WITH (NOLOCK)
)

, LoginlogWithLogoutTimeNotNull AS (
SELECT a.Id, a.Name, a.LoginTime, DATEADD(SECOND, -1, b.LoginTime) [Logout_New]
FROM LoginOutTable a WITH (NOLOCK)
JOIN LoginOutTable b ON b.Name = a.Name
AND (a.RowNo = b.RowNo - 1)
AND (a.LogoutTime IS NULL)
UNION
SELECT a.Id, a.Name, a.LoginTime, a.LogoutTime
FROM LoginOutTable a WITH (NOLOCK)
WHERE (a.LogoutTime IS NOT NULL)
)

SELECT l.*
FROM LoginlogWithLogoutTimeNotNull l

最後查詢結果發現少了幾筆資料，不知道該怎麼樣才能夠查詢到我要的結果，有大大可以幫忙嗎 :)

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 8 級 ‧ 2022-03-18 17:01:53

最佳解答

CREATE TABLE LoginLog (
Id INT, 
Name VARCHAR(50), 
LoginTime DATETIME, 
LogoutTime DATETIME);

INSERT INTO LoginLog
VALUES (1, 'a', '2022/03/01 09:00:00', '2022/03/01 15:00:00'),
(2, 'a', '2022/03/02 02:00:00', null),
(3, 'a', '2022/03/03 10:00:00', '2022/03/18 11:00:00'),
(4, 'a', '2022/03/04 07:00:00', null),
(5, 'b', '2022/03/04 07:00:00', '2022/03/18 08:00:00'),
(6, 'a', '2022/03/05 020:00:00', null),
(7, 'b', '2022/03/06 08:00:00', null),
(8, 'a', '2022/03/06 10:00:00', '2022/03/18 11:00:00'),
(9, 'b', '2022/03/04 16:00:00', null);

SELECT *,
CASE WHEN LogoutTime IS NOT NULL THEN LogoutTime ELSE
DATEADD(SECOND, -1,
LEAD(LoginTime, 1,null)
OVER (PARTITION BY Name ORDER BY LoginTime)) 
END AS LogoutTime_New
FROM LoginLog
ORDER BY Id

Demo