sql – 使用递归公用表表达式从两个表中查找连续的no.s

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!