我有一張這樣的表：
| 日期 | Consec_Days |
|---|---|
| 2015-01-01 | 1 |
| 2015-01-03 | 1 |
| 2015-01-06 | 1 |
| 2015-01-07 | 2 |
| 2015-01-09 | 1 |
| 2015-01-12 | 1 |
| 2015-01-13 | 2 |
| 2015-01-14 | 3 |
| 2015-01-17 | 1 |
我需要對每個連續分組的最大值（天）求和，其中 Consec_Days > 1。所以正確的結果是 5 天。
uj5u.com熱心網友回復：
這是一種間隙和島嶼問題。
解決方法有很多，這里只介紹一個簡單的
- 使用獲取每個組的起點
LAG - 使用視窗條件計數計算分組 ID
- 按該 ID 分組并取最高總和
WITH StartPoints AS (
SELECT *,
IsStart = CASE WHEN LAG(Consec_Days) OVER (ORDER BY Date) = 1 THEN 1 END
FROM YourTable t
),
Groupings AS (
SELECT *,
GroupId = COUNT(IsStart) OVER (ORDER BY Date)
FROM StartPoints
WHERE Consec_Days > 1
)
SELECT TOP (1)
SUM(Consec_Days)
FROM Groupings
GROUP BY
GroupId
ORDER BY
SUM(Consec_Days) DESC;
資料庫<>小提琴
uj5u.com熱心網友回復：
with cte as (
select Consec_Days,
coalesce(lead(Consec_Days) over (order by Date), 1) as next
from YourTable
)
select sum(Consec_Days)
from cte
where Consec_Days <> 1 and next = 1
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/353031.html
標籤：sql-server 和 最大限度