我有以下表格：
Actual Optional
------ --------
4 3
13 6
20 7
26 14
19
21
27
28
我要做的是选择：
1) all the values from “Actual” Table.
2) select values from “Optional” table if they form a consecutive
series with “actual” table values
预期的结果是：
Answer
------
4
13
20
26
3 --because it is consecutive to 4 (i.e 3=4-1)
14 --14=13+1
19 --19=20-1
21 --21=20+1
27 --27=26+1
28 --this is the important case.28 is not consecutive to 26 but 27
--is consecutive to 26 and 26,27,28 together form a series.
我使用递归cte编写了一个查询但是它永远循环并且在递归达到100级后失败.
我面临的问题是27场比赛26场比赛,28场比赛27场比赛27场比赛27场比赛28场比赛27场比赛……(永远)
这是我写的查询：
with recurcte as
(
select num as one,num as two from actual
union all
select opt.num as one,cte.two as two
from recurcte cte join optional opt
on opt.num+1=cte.one or opt.num-1=cte.one
)select * from recurcte
解决方法
;WITH Combined
AS (SELECT 1 AS Actual,N
FROM (VALUES(4),(13),(20),(26)) Actual(N)
UNION ALL
SELECT 0 AS Actual,N
FROM (VALUES(3),(6),(7),(14),(19),(21),(27),(28)) Optional (N)),T1
AS (SELECT *,N - DENSE_RANK() OVER (ORDER BY N) AS Grp
FROM Combined),T2
AS (SELECT *,MAX(Actual) OVER (PARTITION BY Grp) AS HasActual
FROM T1)
SELECT DISTINCT N
FROM T2
WHERE HasActual = 1
