您可以使用以下封装在CTE
为了将序列号分配给序列中包含的值:
;WITH Seq AS (
SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
FROM (VALUES(1, 5), (2, 9), (3, 6)) x(k,v)
)
Output:
v rn
-------
5 1
9 2
6 3
使用上面的CTE
您可以识别岛,即包含整个序列的连续行的切片:
;WITH Seq AS (
SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
FROM (VALUES(1, 5), (2, 9), (3, 6)) x(k,v)
), Grp AS (
SELECT [Key], [Value],
ROW_NUMBER() OVER (ORDER BY [Key]) - rn AS grp
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Value = s.v
)
SELECT *
FROM Grp
Output:
Key Value grp
-----------------
1 5 0
2 9 0
3 6 0
6 5 3
7 9 3
8 6 3
grp
字段可以帮助您准确识别这些岛屿。
您现在需要做的就是过滤掉部分组:
;WITH Seq AS (
SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
FROM (VALUES(1, 5), (2, 9), (3, 6)) x(k,v)
), Grp AS (
SELECT [Key], [Value],
ROW_NUMBER() OVER (ORDER BY [Key]) - rn AS grp
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Value = s.v
)
SELECT g1.[Key], g1.[Value]
FROM Grp AS g1
INNER JOIN (
SELECT grp
FROM Grp
GROUP BY grp
HAVING COUNT(*) = 3 ) AS g2
ON g1.grp = g2.grp
演示在这里
Note:这个答案的最初版本使用了INNER JOIN
to Seq
。如果表包含类似的值,这将不起作用5, 42, 9, 6
, as 42
将被过滤掉INNER JOIN
并且该序列被错误地识别为有效序列。此编辑归功于@HABO。