SQL server 按照级别合并重叠时间段,重叠日期是上一个结束日期
时间: 2024-03-27 12:38:34 浏览: 19
您可以使用 SQL Server 中的窗口函数和递归查询来合并重叠时间段。
以下是一个使用窗口函数的示例查询,该查询将按照级别合并重叠时间段,重叠日期是上一个结束日期:
```sql
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Level ORDER BY StartDate) AS rn,
CASE WHEN LAG(EndDate) OVER (PARTITION BY Level ORDER BY StartDate) >= StartDate THEN 1 ELSE 0 END AS Overlap
FROM your_table
),
RecursiveCTE AS (
SELECT Level, StartDate, EndDate
FROM CTE
WHERE rn = 1
UNION ALL
SELECT c.Level, c.StartDate, CASE WHEN rc.EndDate >= c.EndDate THEN rc.EndDate ELSE c.EndDate END
FROM CTE c
JOIN RecursiveCTE rc ON c.Level = rc.Level AND c.rn = rc.rn + 1 AND c.Overlap = 1
)
SELECT Level, MIN(StartDate) AS StartDate, MAX(EndDate) AS EndDate
FROM RecursiveCTE
GROUP BY Level;
```
要使用此查询,您需要将 `your_table` 替换为您的表名,并将 `Level`、`StartDate` 和 `EndDate` 替换为您的列名。
请注意,此查询假定每个级别的时间段都是连续的。如果存在断裂的时间段,则需要进行一些修改。