SQL Server向上，向下递归查询相关

无限向上查询

WITH T
AS(
SELECT uid,upid,uloginname FROM tb_User WHERE uId=5
UNION ALL
SELECT U.uid,U.upid,U.uloginname
FROM tb_User U INNER JOIN T ON U.uid=T.upid --and u.ulev>0 递归条件
)
SELECT * FROM T --where t.uid!=5 --结果条件
无限向下查询

WITH T
AS(
SELECT uid,upid,uloginname FROM tb_User WHERE uId=5
UNION ALL
SELECT U.uid,U.upid,U.uloginname
FROM tb_User U INNER JOIN T ON u.upid=t.uid --and u.ulev>0 递归条件
)
SELECT * FROM T --where t.uid!=5 --结果条件
 

区别在于：u.uid=t.upid ( 向上查 ) ，u.upid=t.uid ( 向下查 )
向上递归和向下递归，其实就是调换上下级查询。

 

注意事项一：

如果要同时查询出所有上级，下级，可以使用两个WITH AS，前一个查询后加上分号";"

注意事项二：

SQL Server默认查询100级，可以在查询后增加OPTION(MAXRECURSION 0)，0为不限制，其余正整数对应相关的查询层级。

举例：

 1 DECLARE @num1 INT
 2 DECLARE @num2 INT
 3 WITH temp
 4      AS (SELECT *
 5          FROM   contract_info
 6          WHERE  id = (SELECT contract_info.id
 7                       FROM   contract_info
 8                              JOIN live_info
 9                                ON live_info.contractId = contract_info.id
10                              JOIN live_process
11                                ON live_process.shopInOutId = live_info.id
12                       WHERE  live_process.id = 'E0BDEAE5-BB9E-477E-BE29-30F1DDC5D4F0')
13          UNION ALL
14          SELECT c.*
15          FROM   contract_info c,
16                 temp t
17          WHERE  c.previousId = t.id)
18 SELECT
19 --ci.previousId 原合同,ci.id 合同号,ci.contractNumber 编号,*
20 @num1 = Count(UniquesystemID)
21 FROM   live_process lp
22        JOIN live_info li
23          ON li.id = lp.shopInOutId
24        JOIN contract_info ci
25          ON ci.id = li.contractId
26 WHERE  lp.comid = '433222a1_5cde_4276_9118_b6ef910c5845'
27        AND UniquesystemID = '123654'
28        AND UniquesystemID != ''
29        AND lp.id != 'E0BDEAE5-BB9E-477E-BE29-30F1DDC5D4F0'
30        AND ci.id NOT IN (SELECT id
31                          FROM   temp)
32 OPTION(MAXRECURSION 0)
33 
34 ;
35 
36 WITH temp
37      AS (SELECT *
38          FROM   contract_info
39          WHERE  id = (SELECT contract_info.id
40                       FROM   contract_info
41                              JOIN live_info
42                                ON live_info.contractId = contract_info.id
43                              JOIN live_process
44                                ON live_process.shopInOutId = live_info.id
45                       WHERE  live_process.id = 'E0BDEAE5-BB9E-477E-BE29-30F1DDC5D4F0')
46          UNION ALL
47          SELECT c.*
48          FROM   contract_info c,
49                 temp t
50          WHERE  t.previousId = c.id)
51 SELECT
52 --ci.previousId 原合同,ci.id 合同号,ci.contractNumber 编号,*
53 @num2 = Count(UniquesystemID)
54 FROM   live_process lp
55        JOIN live_info li
56          ON li.id = lp.shopInOutId
57        JOIN contract_info ci
58          ON ci.id = li.contractId
59 WHERE  lp.comid = '433222a1_5cde_4276_9118_b6ef910c5845'
60        AND UniquesystemID = '123654'
61        AND UniquesystemID != ''
62        AND lp.id != 'E0BDEAE5-BB9E-477E-BE29-30F1DDC5D4F0'
63        AND ci.id NOT IN (SELECT id
64                          FROM   temp)
65 OPTION(MAXRECURSION 0)
66 SELECT @num1 + @num2

 

posted @ 2019-11-13 10:13  ittalk  阅读(584)  评论(0)    收藏  举报