我有一個有 2 列的表：
val具有值：0 或 1id具有唯一識別符號
select 0 val, 0 id into #tmp union all
select 1, 1 union all
select 1, 2 union all
select 0, 3 union all
select 1, 4 union all
select 1, 5 union all
select 1, 6 union all
select 1, 7 union all
select 1, 8 union all
select 1, 9 union all
select 1, 10
我如何找到連續 6 個值 = 1 的 id。
在上面的示例中：id = 9，id = 10。
最好不要使用回圈（游標或 while），而是使用 sum() 之類的東西。
uj5u.com熱心網友回復：
為什么不LAG()（但您需要一個訂單列）：
SELECT id
FROM (
SELECT
id,
val,
val1 = LAG(val, 1) OVER (ORDER BY id),
val2 = LAG(val, 2) OVER (ORDER BY id),
val3 = LAG(val, 3) OVER (ORDER BY id),
val4 = LAG(val, 4) OVER (ORDER BY id),
val5 = LAG(val, 5) OVER (ORDER BY id)
FROM YourTable
) t
WHERE val = 1 AND val1 = 1 AND val2 = 1 AND val3 = 1 AND val4 = 1 AND val5 = 1
uj5u.com熱心網友回復：
另一種方法是在 LAG 值上使用 ROW_NUMBER
declare @tmp table (val int, id int)
insert into @tmp values
(0, 0), (1, 1), (1, 2), (0, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10)
select 0, 0 union all
select 1, 1 union all
select 1, 2 union all
select 0, 3 union all
select 1, 4 union all
select 1, 5 union all
select 1, 6 union all
select 1, 7 union all
select 1, 8 union all
select 1, 9 union all
select 1, 10
select t2.id,
t2.islandcount
from ( select t.id,
t.val,
t.priorval,
row_number() over (partition by t.val, t.priorval order by t.id) as islandcount
from ( select id,
val,
lag(val, 1) over (order by id) priorval
from @tmp
) t
) t2
where t2.islandcount >= 6
結果是
id islandcount
9 6
10 7
在這個 DBFiddle 中自己嘗試一下
這種方法的優點是您可以輕松地將值從 6 設定為任何其他值
編輯
正如評論中提到的@Zhorov，我的代碼中有一個缺陷，當您添加某些行時它會回傳錯誤的結果
該解決方案將解決該問題，它基于@SalmanA 的解決方案，因此接受答案的功勞應該歸他所有
declare @tmp table (val int, id int)
insert into @tmp values
(0, 0), (1, 1), (1, 2), (0, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10)
-- these are the certains rows added
,(0, 11), (1, 12), (1, 13)
select t.id,
t.val,
t.islandcount
from ( select id,
val,
sum(val) over (order by id rows between 5 preceding and current row) as islandcount
from @tmp
) t
where t.islandcount >= 6
order by t.id
又是一個 DBFiddle
uj5u.com熱心網友回復：
您可以在恰好包含 6 行（前 5 行加上當前行）的視窗框架上使用運行總和：
with cte as (
select *, sum(val) over (
order by id
rows between 5 preceding and current row
) as rsum
from #tmp
)
select *
from cte
where rsum = 6
調整視窗的大小和 where 子句以匹配所需的值。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/431105.html