SQL找子元素

 1 DECLARE @tb TABLE
 2 (
 3     id BIGINT NOT NULL
 4 )
 5 DECLARE @tmp TABLE
 6 (
 7     id BIGINT NOT NULL
 8 )
 9 DECLARE @tmp2 TABLE
10 (
11     id BIGINT NOT NULL
12 )
13 DECLARE @i INT = 0
14 
15 INSERT @tmp
16 VALUES
17 (1)
18 
19 INSERT @tb
20 SELECT id
21 FROM @tmp
22 
23 WHILE @i < 1000
24 BEGIN
25     INSERT @tmp2
26     SELECT main.ID
27     FROM UserNodes main
28         INNER JOIN @tmp t
29             ON main.ParentID = t.id
30 
31     IF EXISTS (SELECT 1 FROM @tmp2)
32     BEGIN
33 
34         DELETE t
35         FROM @tmp2 t
36             INNER JOIN @tb tb
37                 ON tb.id = t.id
38 
39         INSERT @tb
40         SELECT id
41         FROM @tmp2
42 
43         DELETE FROM @tmp
44 
45         INSERT @tmp
46         SELECT id
47         FROM @tmp2
48 
49         DELETE FROM @tmp2
50     END
51     ELSE
52         BREAK
53     SET @i = @i + 1
54 END
55 
56 SELECT *
57 FROM @tb

 

转载于:https://www.cnblogs.com/lizhanglong/p/8593040.html