在使用sql Server 2012或更高版本的系统中，您可以LAG()用来获取上一个最终日期并调整初始日期：
with ReservationAdjusted as (
select *,
lag(LastDay) over(partition by HouseID order by InitialDay, LastDay) as PrevIoUsLast
from Reservation
)
select HouseId,
sum(case when PrevIoUsLast>LastDay then 0 -- fully contained in the prevIoUs reservation
when PrevIoUsLast>=InitialDay then datediff(day,PrevIoUsLast,LastDay) -- overlap
else datediff(day,InitialDay,LastDay)+1 -- no overlap
end) as Days
from ReservationAdjusted
group by HouseId
情况是：
请注意，我们不需要额外的条件来保留a，HouseID因为默认情况下该LAG()函数NULL在没有上一行时返回，并且与null的比较始终为false。
样本输入和输出：
| HouseId | InitialDay | LastDay |
|---------|------------|------------|
| 1 | 2017-09-18 | 2017-09-20 |
| 1 | 2017-09-18 | 2017-09-22 |
| 1 | 2017-09-21 | 2017-09-22 |
| 19 | 2017-09-18 | 2017-09-27 |
| 19 | 2017-09-24 | 2017-09-26 |
| 19 | 2017-09-29 | 2017-09-30 |
| 20 | 2017-09-19 | 2017-09-22 |
| 20 | 2017-09-22 | 2017-09-26 |
| 20 | 2017-09-24 | 2017-09-27 |
| HouseId | Days |
|---------|------|
| 1 | 5 |
| 19 | 12 |
| 20 | 9 |
602392714
清零编程群