您可以使用此查询使用 ROW_NUMBER() 来获得最小值 . ranges 是您的CTE的结果, table1 是您有日期的另一个表 .
select x.somedate
from
(select t.somedate,
ROW_NUMBER() OVER (PARTITION BY r.first_day_of_month, r.last_day_of_moth ORDER BY t.somedate) rownumber
from ranges r
inner join table1 t
on r.first_day_of_month <= t.somedate and r.last_day_of_moth >= t.somedate) x
where x.rownumber = 1
如果要获取所有范围并仅包含匹配范围的天数并为其他天数显示null,则可以再次加入 ranges :
select ranges.first_day_of_month, ranges.last_day_of_moth, x.somedate
from
ranges
left join
(select t.somedate, r.first_day_of_month, r.last_day_of_moth,
ROW_NUMBER() OVER (PARTITION BY r.first_day_of_month, r.last_day_of_moth ORDER BY t.somedate) rownumber
from ranges r
inner join table1 t
on r.first_day_of_month <= t.somedate and r.last_day_of_moth >= t.somedate) x
on x.first_day_of_month = ranges.first_day_of_month and x.last_day_of_moth = ranges.last_day_of_moth
where isnull(x.rownumber, 1) = 1