我创建了以下表格:
| id | val |
|---|---|
| 1 | 0 |
| 2 | 1 |
| 3 | 0 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 0 |
Create Table Test3
(
id int,
val int
)
Insert Into Test3 Values (1,0),(2,1),(3,0),(4,1),(5,1),(6,1),(7,0)
我想要输出为
| id |
|---|
| 4 |
| 5 |
| 6 |
因为在[VAL]列中连续的三个值相同.如果第7行也有1，则该id号也应该反映在输出中.
我怎么才能解决这个问题？
我已经try 了Self Join，但没有得到预期的输出. 提前谢谢您！
您可以使用LAG和LEAD来执行此操作:
SELECT id, val
FROM (
SELECT *, CASE WHEN (Val = LAG(Val,1) OVER (ORDER BY ID) AND Val = LAG(Val,2) OVER (ORDER BY ID))
OR (Val = LEAD(Val,1) OVER (ORDER BY ID) AND Val = LEAD(Val,2) OVER (ORDER BY ID))
OR (Val = LEAD(Val,1) OVER (ORDER BY ID) AND Val = LAG(Val,1) OVER (ORDER BY ID))
THEN 1 ELSE 0 END AS Cnte
FROM Test3
) a
WHERE Cnte >= 1;
| ID | Val |
|---|---|
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
如果您扩展示例数据:
VALUES (1,0),(2,1),(3,0),(4,1),(5,1),(6,1),(7,0),(8,3),(9,3),(10,3),(11,10),(12,4),(13,4),(15,4),(16,4)
| ID | Val |
|---|---|
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 8 | 3 |
| 9 | 3 |
| 10 | 3 |
| 12 | 4 |
| 13 | 4 |
| 15 | 4 |
| 16 | 4 |