我有一個 SQL Server 資料庫，其中包含一個從考勤設備獲取資料的表。
它看起來像這樣：
| ID | 用戶身份 | 日期 | 時間 | 指示方向 |
|---|---|---|---|---|
| 1988781 | 25000 | 2022/01/11 | 10:02 | 1 |
| 1988782 | 25000 | 2022/01/11 | 10:03 | 1 |
| 1988783 | 25000 | 2022/01/11 | 10:04 | 1 |
| 1988784 | 25000 | 2022/01/11 | 12:30 | 2 |
| 1988785 | 25000 | 2022/01/11 | 12:31 | 2 |
| 1988786 | 25001 | 2022/01/11 | 10:00 | 1 |
| 1988787 | 25001 | 2022/01/11 | 12:30 | 2 |
| 1988788 | 25002 | 2022/01/11 | 10:15 | 1 |
| 1988789 | 25002 | 2022/01/11 | 10:16 | 1 |
| 1988790 | 25002 | 2022/01/11 | 12:19 | 2 |
如何過濾每個用戶 ID 的資料以使 SignDirection = 1 的時間更短，而 SignDirection = 2 的時間更長？
像是 ：
| ID | 用戶身份 | 日期 | 時間 | 指示方向 |
|---|---|---|---|---|
| 1988781 | 25000 | 2022/01/11 | 10:02 | 1 |
| 1988785 | 25000 | 2022/01/11 | 12:31 | 2 |
| 1988786 | 25001 | 2022/01/11 | 10:00 | 1 |
| 1988787 | 25001 | 2022/01/11 | 12:30 | 2 |
| 1988788 | 25002 | 2022/01/11 | 10:15 | 1 |
| 1988790 | 25002 | 2022/01/11 | 12:19 | 2 |
我努力了
select distinct
但沒有成功。
uj5u.com熱心網友回復：
您可以計算 2 row_number，向上和向下。
然后過濾它們。
SELECT ID, UserID, [Date], [Time], SignDirection
FROM
(
SELECT *
, ROW_NUMBER() OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time] ASC) rn_asc
, ROW_NUMBER() OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time] DESC) rn_desc
FROM your_time_and_attendance_table t
) q
WHERE ((SignDirection = 1 AND rn_asc = 1) OR
(SignDirection = 2 AND rn_desc = 1))
ORDER BY ID, UserID, [Date], [Time], SignDirection
uj5u.com熱心網友回復：
@LukStorms 給出的答案很好，但它需要對第二個行號進行另一種排序。
您可以改為使用LEAD相同的排序來檢測最后一行
SELECT
ID,
UserID,
[Date],
[Time],
SignDirection
FROM
(
SELECT *
, ROW_NUMBER() OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time]) rn_asc
, LEAD(Time) OVER (PARTITION BY UserID, SignDirection, [Date] ORDER BY [Time]) nextTime
FROM YourTable t
) q
WHERE ((SignDirection = 1 AND rn_asc = 1) OR
(SignDirection = 2 AND nextTime IS NULL))
ORDER BY
ID,
UserID,
[Date],
[Time],
SignDirection;
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/410548.html
標籤：
上一篇：EFCore：與FirstOrDefaultAsync的區別
下一篇：從當年的周數中減去N