SQL -查找日期最接近但列值不同的行

Question

我是新的SQL，我需要一个帮助。

我有一个标签，我需要找到标签中任何项目B的项目a与最近的日期。在这种情况下，A与02.09.2021 04:25:30

<表类> 。 项目 tbody><<tr>07.09.2021 05:02:0506.09.2021 05:01:0205.09.2021 05:00:0204.09.2021 04:59:0103.09.2021 04:58:0302.09.2021 04:56:5502.09.2021 04:33:56 td>02.09.2021 04:25:30

WITH CTE(DATE,ITEM)AS
(
SELECT '20210907 05:02:05' ,    'A'UNION ALL
SELECT   '20210906 05:01:02' ,  'A'UNION ALL
SELECT   '20210905 05:00:02' ,  'A'UNION ALL
SELECT'20210904 04:59:01' , 'A'UNION ALL
SELECT'20210903 04:58:03' , 'A'UNION ALL
SELECT'20210902 04:56:55' , 'A'UNION ALL
SELECT'20210902 04:33:56' , 'B'UNION ALL
SELECT'20210902 04:25:30' , 'A'
)
SELECT 
CAST(C.DATE AS DATETIME)X_DATE,C.ITEM,Q.CLOSEST
FROM CTE AS C
OUTER APPLY
(
SELECT TOP 1 CAST(X.DATE AS DATETIME)CLOSEST
FROM CTE AS X
WHERE X.ITEM='A'AND CAST(X.DATE AS DATETIME)<CAST(C.DATE AS DATETIME)
ORDER BY CAST(X.DATE AS DATETIME) ASC
)Q
WHERE C.ITEM='B'

您可以像上面的查询一样使用OUTER apply -方法。还请注意日期-时间-列(DATE)是以符合iso标准的格式

书写的。

您的数据只有两列。如果您想要唯一最接近的A时间戳，那么最快的方法可能是窗口函数:

select t.*,
(case when prev_a_date is null then next_a_date
when next_a_date is null then prev_a_date
when datediff(second, prev_a_date, date) <= datediff(second, date, next_a_date) then prev_a_date
else next_a_date
end) as a_date
from (select t.*,
max(case when item = 'A' then date end) over (order by date) as prev_a_date,
min(case when item = 'A' then date end) over (order by date desc) as next_a_date
from t
) t
where item = 'B';

使用秒来度量时间差，但如果合适的话，您可以使用更小的单位。

如果您有更多来自"A"的列，也可以使用apply执行此操作。需要的行:

select tb.*, ta.*
from t b outer apply
(select top (1) ta.*
from t ta
where item = 'A'
order by abs(datediff(second, a.date, b.date))
) t
where item = 'B';