如何获得适合日期范围的行，以及每个ID在给定日期范围之前的最近一行

Question

我想为查询提供一个日期范围，例如2021年1月11日至2021年11月30日。从那里，我想获得该日期范围内的所有相关行，这非常简单——只需简单地选择一个基于截止日期和起始日期的where子句。但在此之后，我想获得日期范围之前的最近一行(如果存在(，仅用于日期范围内出现的外键ID。

举个例子，

日期

ID	FK ID
1	101	2021年5月9日
2	101	2021年10月29日
3	101	2021年5月11日
4	201	2021年11月20日
5	301	2021年8月13日
6	401	2021年1月11日
7	401	2021年11月23日

您可以使用cte来选择范围内的行，并相对于此cte 添加更多行

with cte as (
SELECT *
FROM tb
WHERE [Date] >= '11/1/2021' AND [Date] <= '11/30/2021'
)
select * 
from cte
union all
select *
from (
select top(1) with ties *
from tb
where exists (select 1 from cte where cte.fk_id = tb.fk_id)
and [Date] < '11/1/2021'
order by row_number() over(partition by fk_id order by [date] desc)
) t
order by id;

db<gt；小提琴

以下查询生成您想要的输出

WITH cte AS(
SELECT ID,FK_ID,[DATE]
FROM tb
WHERE [DATe] >= '11/1/2021' AND [Date] <= '11/30/2021')
SELECT * 
FROM cte    
UNION ALL  
SELECT ID,FK_ID,[DATE]
FROM
(SELECT *,
CASE WHEN LEAD([Date]) over(ORDER BY ID) >= '11/1/2021' AND LEAD([Date]) over(ORDER BY ID) <= '11/30/2021' THEN 1 ELSE 0 END AS rnk
FROM tb) t
WHERE rnk = 1 AND [DATe] < '11/1/2021' AND FK_ID IN (SELECT FK_ID FROM cte)
ORDER BY ID

db<gt；小提琴