我有一個從最舊到最新排序的 ID 和日期串列。我想選擇與下一個日期（具有匹配 ID）的差異為 45 天或更大的所有 ID 和日期。
202185, 2021-10-01 09:35:000
202185, 2021-10-02 09:36:000
202185, 2021-10-03 09:14:000
202185, 2022-02-01 09:22:000
202185, 2022-02-02 09:23:000
301133, 2021-11-01 09:35:000
301133, 2021-11-02 09:36:000
301133, 2021-11-03 09:14:000
301133, 2021-12-06 09:22:000
301133, 2022-01-25 09:23:000
選擇回報：
202185, 2022-02-01 09:22:000
301133, 2022-01-25 09:23:000
是否有使用 SQL Server 處理此問題的有效方法？
謝謝！
uj5u.com熱心網友回復：
select id, date
from (
select id, [date],
datediff(day, lag([date]) over (partition by id order by [date]), [date]) as daydiff
from [MyTable]
) t
where t.dayDiff >= 45
order by id, [date];
在這里看到它：
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=69b5b976154d52a04333fbb1b771b3e1
uj5u.com熱心網友回復：
SELECT A.* FROM MyTable A
WHERE A.date >
( SELECT DATEADD(day, 45, MAX(b.date))
FROM MyTable B
WHERE B.ID = A.ID
AND B.date < A.date
)
在這里看到它：
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=038a3d02461f0ea54a14140e6be22370
uj5u.com熱心網友回復：
RHere 是LAG()在 CTE 中使用的解決方案。
CREATE TABLE idz( ID INT, DAT DATETIME); INSERT INTO idz VALUES (202185, '2021-10-01 09:35:000'), (202185, '2021-10-02 09:36:000'), (202185, '2021-10-03 09:14:000'), (202185, '2022-02-01 09:22:000'), (202185, '2022-02-02 09:23:000'), (301133, '2021-11-01 09:35:000'), (301133, '2021-11-02 09:36:000'), (301133, '2021-11-03 09:14:000'), (301133, '2021-12-06 09:22:000'), (301133, '2022-01-25 09:23:000');
WITH i AS ( SELECT ID, DAT, LAG(DAT) OVER ( PARTITION BY ID ORDER BY DAT) DAT_1 FROM idz) SELECT ID, DAT, DAT_1, DATEDIFF(DAY,DAT_1,DAT) DD FROM i WHERE DATEDIFF(DAY,DAT_1,DAT)>= 45
身份證 | 資料 | DAT_1 | DD -----: | :------------------------ | :------------------------ | --: 202185 | 2022-02-01 09:22:00.000 | 2021-10-03 09:14:00.000 | 121 301133 | 2022-01-25 09:23:00.000 | 2021-12-06 09:22:00.000 | 50
db<>在這里擺弄
由于shawnt00的信用
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/435475.html