Sql递归查询来识别家谱的2个用户之间的关系 [英] Sql Recursive query to identify relation between 2 users of family tree

查看：54 发布时间：2021/7/5 19:33:12 sql-server sql-server-2008 recursive-query

本文介绍了Sql递归查询来识别家谱的2个用户之间的关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

用户数据

 用户 ID 显示名称12 拉胡尔13 安贾利14 法鲁克15 沙比娜16 夏奇拉17 阿里18 罗纳克19 大理20 巴厘岛21 卡利22 卡特里娜飓风23 西塔24 歌谣25 公羊26 夏亚姆27 苏哈娜28 苏哈斯29 拉吉30 塔斯利姆31 瑞提克32 光辉33 迪皮卡34 布什第35话第36话37 萨尔曼第38话第39话40 塔兹41 三木42 克里什第43话

家谱数据

ID UserID RelativeId Relation1 12 13 配偶3 12 15 女儿4 12 16 女儿5 12 17 儿子6 12 18 儿子7 13 12 配偶9 13 15 女儿10 13 16 女儿11 13 17 儿子12 13 18 儿子13 14 15 配偶14 14 20 儿子15 14 21 女儿16 14 19 女儿17 15 12 父亲18 15 13 妈妈19 15 14 配偶20 15 16 姐姐21 15 18 兄弟22 15 20 儿子23 15 19 女儿24 15 21 女儿25 16 12 父亲26 16 13 妈妈27 16 17 配偶28 16 14 兄弟29 16 18 兄弟30 16 23 女儿31 16 24 女儿32 16 25 儿子33 16 26 儿子34 17 16 配偶35 17 23 女儿36 17 24 女儿37 17 25 儿子38 17 26 儿子39 18 12 父亲40 18 13 妈妈41 18 19 配偶42 18 27 女儿43 18 28 儿子44 18 29 儿子45 18 15 姐妹46 18 16 姐姐47 19 14 父亲48 19 15 母亲49 19 18 配偶50 19 27 女儿51 19 28 儿子52 19 29 儿子53 19 20 兄弟54 19 21 姐妹55 20 14 父亲56 20 15 母亲57 20 21 姐妹58 20 19 姐妹59 20 40 配偶60 20 42 儿子61 20 43 儿子62 21 14 父亲63 21 15 母亲64 21 25 配偶65 21 40 女儿66 21 41 儿子67 21 20 兄弟68 21 19 姐妹102 22 28 配偶103 22 31 儿子69 23 16 母亲70 23 17 父亲71 23 24 姐妹72 23 25 兄弟73 23 26 兄弟74 24 16 妈妈75 24 17 父亲76 24 23 姐妹77 24 25 兄弟78 24 26 兄弟79 25 16 母亲80 25 17 父亲81 25 23 姐妹82 25 24 姐妹83 25 26 兄弟84 25 21 配偶85 25 40 女儿86 25 41 儿子87 26 16 妈妈88 26 17 父亲89 26 23 姐妹90 26 24 姐姐91 26 25 兄弟92 26 27 配偶93 26 34 儿子94 26 31 女儿95 27 18 父亲96 27 19 母亲97 27 26 配偶98 27 28 兄弟99 27 29 兄弟100 27 34 儿子101 27 35 女儿104 28 18 父亲105 28 19 母亲106 28 27 姐妹107 28 29 兄弟108 28 22 配偶109 28 31 儿子110 29 18 父亲111 29 19 妈妈112 29 27 姐妹113 29 28 兄弟114 29 30 配偶115 29 32 儿子116 30 29 配偶117 30 32 儿子118 31 28 父亲119 31 22 母亲120 31 34 配偶121 31 36 儿子122 32 29 父亲123 32 30 妈妈124 32 33 配偶125 32 35 配偶126 32 37 儿子127 32 38 女儿128 33 32 配偶129 33 38 女儿130 34 26 父亲131 34 27 母亲132 34 35 姐妹133 34 31 配偶134 34 36 儿子135 35 26 父亲136 35 27 母亲137 35 32 配偶138 35 34 兄弟139 35 37 儿子140 36 34 父亲141 36 31 父亲142 37 35 母亲143 37 32 父亲144 37 38 配偶145 37 39 女儿146 38 32 父亲147 38 33 妈妈148 38 37 配偶149 38 39 女儿150 39 37 父亲151 39 38 母亲152 40 21 母亲153 40 25 父亲154 40 41 兄弟155 40 20 配偶156 40 42 儿子157 40 43 儿子158 41 21 母亲159 41 25 父亲160 41 40 姐姐161 42 20 父亲162 42 40 妈妈163 42 43 兄弟164 43 20 父亲165 43 40 妈妈166 43 42 兄弟

想找到路径以找到 Rahul(Id=12) 和 Tejas(Id=32) 之间的关系提前感谢您的帮助....

以上数据的图表:

由旁观者提供的查询:

声明@Users TABLE(用户 ID INT,用户名 VARCHAR(50))声明@家谱表(用户 ID INT,关系 ID INT，关系 VARCHAR(50))INSERT INTO @Users (UserID,UserName) SELECT 1 ,'Rahul'INSERT INTO @Users (UserID,UserName) SELECT 2 ,'Anjli'INSERT INTO @Users (UserID,UserName) SELECT 3 ,'Ronak'INSERT INTO @Users (UserID,UserName) SELECT 4 ,'Rutu'INSERT INTO @Users (UserID,UserName) SELECT 5 ,'Riya'INSERT INTO @Users (UserID,UserName) SELECT 6 ,'Rishi'INSERT INTO @Users (UserID,UserName) SELECT 7 ,'Harry'INSERT INTO @Users (UserID,UserName) SELECT 8 ,'Dyna'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 2 ,'Wife'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 1 ,'Rahul'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 3 ,'Son'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 4 ,'Wife'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 5 ,'Daughter'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 5 ,'Daughter'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 6 ,'Son'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 6 ,'Son'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 7 ,'Son'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 7 ,'Son'INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 7, 8 ,'Wife'声明@UserID1 INT，@UserID2 INT选择@用户ID1 = 1，@用户ID2 = 6声明@Table TABLE(cnt INT,cpath VARCHAR(50));与家庭 AS(选择  *，1 AS CntLevel,CAST('/' + CAST(ft.UserID AS VARCHAR(10)) + '/' AS VARCHAR(50)) AS CLevel来自 @FamilyTree 英尺哪里 ft.UserID = @UserID1联合所有选择英尺*，fam.CntLevel + 1 AS CntLevel,CAST(fam.Clevel + CAST(ft.UserID AS VARCHAR(10)) + '/'AS VARCHAR(50)) AS CLevel来自@FamilyTree ft 内部连接fam ON ft.UserID = fam.RelationIDWHERE ft.UserID != @UserID1)插入@Table(cnt, cpath)选择 CntLevel,CAST(fam.Clevel + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50))来自家庭WHERE CAST(fam.Clevel + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('/' + CAST(@UserID1 AS VARCHAR(10)) + '/%')AND CAST(fam.Clevel + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('%/' + CAST(@UserID2 AS VARCHAR(10))+ '/')选择不同英尺*从@Table t 内部连接@FamilyTree ft ON PATINDEX('%/' + CAST(ft.UserID AS VARCHAR(10)) + '/%',t.cpath) >0AND PATINDEX('%/' + CAST(ft.RelationID AS VARCHAR(10)) + '/%',t.cpath) >0

解决方案

好吧，复杂的家庭.试试这个.使用最终输出进行自定义.

<前>声明@用户表(用户 ID INT,用户名 VARCHAR(50))声明@FamilyTree TABLE( [ID] int,用户 ID INT,相对 ID INT，关系 VARCHAR(50))插入@Users选择 12,'Rahul' 工会选择 13,'Anjali' 工会选择 14,'Faruk' 工会选择 15,'Shabina' 工会select 16,'夏奇拉'工会select 17,'阿里'工会选择 18,'Ronak' 工会select 19,'大理'工会select 20,'巴厘岛'联盟选择 21,'Kali' 工会select 22,'Katrina' unionselect 23,'Sita' union选择 24,'Gita' union选择 25,'Ram' 联合选择 26,'Shyam' 工会选择 27,'Suhana' 工会选择 28,'Suhas' 工会选择 29,'Raj' 联合select 30,'Taslim' 工会选择 31,'Ritik' 工会select 32,'Tejas'工会选择 33,'Dipika' 工会选择 34,'Bush' 工会选择 35,'Dyna' 工会select 36,'Bushiar' 工会select 37,'Salman' 工会选择 38,'Ruksana' 工会选择 39,'Khushi' 工会选择 40,'Tazz' 工会选择 41,'Miki' 工会选择 42,'Krish' 工会选择 43,'Kumbh'INSERT INTO @FamilyTree ([ID],UserID,RelativeId,Relation)选择 1,12,13,'配偶' 工会选择 3,12,15,'Daughter' union选择 4,12,16,'Daughter' union选择 5,12,17,'Son' 联合选择 6,12,18,'Son' 联合选择 7,13,12,'配偶' 工会选择 9,13,15,'Daughter' union选择 10,13,16,'Daughter' union选择 11,13,17,'Son' 联合选择 12,13,18,'Son' 联合选择 13,14,15,'配偶' 工会选择 14,14,20,'Son' 联合选择 15,14,21,'Daughter' union选择 16,14,19,'Daughter' union选择 17,15,12,'父' 联合选择 18,15,13,'Mother' union选择 19,15,14,'配偶' 工会选择20,15,16,'姐妹'工会选择 21,15,18,'兄弟' 联合选择 22,15,20,'Son' 联合选择 23,15,19,'Daughter' union选择 24,15,21,'Daughter' union选择 25,16,12,'父' 联合选择 26,16,13,'Mother' union选择 27,16,17,'配偶' 工会选择 28,16,14,'兄弟' 联合选择 29,16,18,'兄弟' 联合选择 30,16,23,'Daughter' union选择 31,16,24,'Daughter' union选择 32,16,25,'Son' 联合选择 33,16,26,'Son' 联合选择 34,17,16,'配偶' 工会选择 35,17,23,'Daughter' union选择 36,17,24,'Daughter' union选择 37,17,25,'Son' 联合选择 38,17,26,'Son' 联合选择 39,18,12,'父' 联合选择 40,18,13,'Mother' union选择 41,18,19,'配偶' 工会选择 42,18,27,'Daughter' union选择 43,18,28,'Son' 联合选择 44,18,29,'Son' 联合选择 45,18,15,'Sister' 工会选择 46,18,16,'Sister' 工会选择 47,19,14,'父' 联合选择 48,19,15,'Mother' union选择 49,19,18,'配偶' 工会选择 50,19,27,'Daughter' union选择 51,19,28,'Son' 联合选择 52,19,29,'Son' 联合选择53,19,20,'兄弟'工会选择54,19,21,'姐妹'工会选择 55,20,14,'父' 联合选择 56,20,15,'Mother' union选择57,20,21,'姐妹'工会选择58,20,19,'姐妹'工会选择 59,20,40,'配偶' 工会选择 60,20,42,'Son' 联合选择 61,20,43,'Son' 联合选择 62,21,14,'父' 联合选择 63,21,15,'Mother' union选择 64,21,25,'配偶' 工会选择 65,21,40,'Daughter' union选择 66,21,41,'Son' 联合选择67,21,20,'兄弟'工会选择68,21,19,'姐妹'工会选择 102,22,28,'配偶' 工会选择 103,22,31,'Son' 联合选择 69,23,16,'Mother' union选择 70,23,17,'父' 联合选择71,23,24,'姐妹'工会选择 72,23,25,'兄弟' 工会选择73,23,26,'兄弟'工会选择 74,24,16,'Mother' union选择 75,24,17,'父' 联合选择76,24,23,'姐妹'工会选择 77,24,25,'兄弟' 联合选择 78,24,26,'兄弟' 联合选择 79,25,16,'Mother' union选择 80,25,17,'父' 联合选择81,25,23,'姐妹'工会选择82,25,24,'姐妹'工会选择 83,25,26,'兄弟' 联合选择 84,25,21,'配偶' 工会选择 85,25,40,'Daughter' union选择 86,25,41,'Son' 联合选择 87,26,16,'Mother' union选择 88,26,17,'父' 联合选择89,26,23,'姐妹'工会选择 90,26,24,'Sister' 工会选择 91,26,25,'兄弟' 联合选择 92,26,27,'配偶' 工会选择 93,26,34,'Son' 并集选择 94,26,31,'Daughter' union选择 95,27,18,'父' 联合选择 96,27,19,'Mother' union选择 97,27,26,'配偶' 工会选择 98,27,28,'兄弟' 工会选择 99,27,29,'兄弟' 联合选择 100,27,34,'Son' 联合选择 101,27,35,'Daughter' union选择 104,28,18,'父' 联合选择 105,28,19,'Mother' 联合选择106,28,27,'姐妹'工会选择 107,28,29,'兄弟' 联合选择 108,28,22,'配偶' 工会选择 109,28,31,'Son' 联合选择 110,29,18,'父' 联合选择 111,29,19,'Mother' 工会选择112,29,27,'姐妹'工会选择 113,29,28,'兄弟' 联合选择 114,29,30,'配偶' 工会选择 115,29,32,'Son' 联合选择 116,30,29,'配偶' 工会选择 117,30,32,'Son' 联合选择 118,31,28,'父' 联合选择 119,31,22,'Mother' union选择 120,31,34,'配偶' 工会选择 121,31,36,'Son' 联合选择 122,32,29,'父' 联合选择 123,32,30,'Mother' union选择 124,32,33,'配偶' 工会选择 125,32,35,'配偶' 工会选择 126,32,37,'Son' 联合选择 127,32,38,'Daughter' union选择 128,33,32,'配偶' 工会选择 129,33,38,'Daughter' union选择 130,34,26,'父' 联合选择 131,34,27,'Mother' 工会选择132,34,35,'姐妹'工会选择 133,34,31,'配偶' 工会选择 134,34,36,'Son' 联合选择 135,35,26,'父' 联合选择 136,35,27,'Mother' union选择 137,35,32,'配偶' 工会选择 138,35,34,'兄弟' 联合选择 139,35,37,'Son' 联合选择 140,36,34,'父' 联合选择 141,36,31,'父' 联合选择 142,37,35,'Mother' union选择 143,37,32,'父' 联合选择 144,37,38,'配偶' 工会选择 145,37,39,'Daughter' union选择 146,38,32,'父' 联合选择 147,38,33,'Mother' union选择 148,38,37,'配偶' 工会选择 149,38,39,'Daughter' union选择 150,39,37,'父' 联合选择 151,39,38,'Mother' union选择 152,40,21,'Mother' union选择 153,40,25,'父' 联合选择 154,40,41,'兄弟' 联合选择 155,40,20,'配偶' 工会选择 156,40,42,'Son' 联合选择 157,40,43,'Son' 联合选择 158,41,21,'Mother' union选择 159,41,25,'父' 联合选择160,41,40,'姐妹'工会选择 161,42,20,'父' 联合选择 162,42,40,'Mother' union选择 163,42,43,'兄弟' 联合选择 164,43,20,'父' 联合选择 165,43,40,'Mother' union选择 166,43,42,'兄弟'声明@UserID1 INT，@UserID2 INT选择@用户ID1 = 12，@用户ID2 = 32--user1 的后代;与famDes作为(选择 * ，cast(ltrim(userid) as varchar(max)) as [path],cast(relation as varchar(max)) as [path2]来自@FamilyTree其中 UserID=@UserID1 和 Relation in ('Son','Daughter')联合所有选择英尺*，cast([path]+'.'+ltrim(ft.userid) as varchar(max)),cast([path2]+'.'+ft.relation as varchar(max))来自@FamilyTree ftft.UserID=famDes.RelativeId 上的内部连接 famDes 和 ('Son','Daughter') 中的 ft.Relation),--user1 的后裔famAsc作为(选择 * ，cast(ltrim(userid) as varchar(max)) as [path],cast(relation as varchar(max)) as [path2]来自@FamilyTree其中 UserID=@UserID1 和 Relation in ('Father','Mother')联合所有选择英尺*，cast([path]+'.'+ltrim(ft.userid) as varchar(max)),cast([path2]+'.'+ft.relation as varchar(max))来自@FamilyTree ft在 ft.UserID=famAsc.RelativeId 和 ft.Relation 上内部连接 famAsc ('Father','Mother'))选择*从家庭在哪里相对ID=@UserID2联合所有选择*从famAsc在哪里相对ID=@UserID2联合所有--如果user1和user2是兄弟或配偶选择* ,cast(ltrim(userid) as varchar(max)) as [path],cast(relation as varchar(max)) as [path2]来自@FamilyTree在哪里用户 ID 在 (@UserID1,@UserID2)和关系('兄弟'，'姐妹'，'配偶')和(@UserID2，@UserID1)中的relativeID

Users Data

    UserId  DisplayName
12  Rahul
13  Anjali
14  Faruk
15  Shabina
16  Shakira
17  Ali
18  Ronak
19  Dali
20  Bali
21  Kali
22  Katrina
23  Sita
24  Gita
25  Ram
26  Shyam
27  Suhana
28  Suhas
29  Raj
30  Taslim
31  Ritik
32  Tejas
33  Dipika
34  Bush
35  Dyna
36  Bushiar
37  Salman
38  Ruksana
39  Khushi
40  Tazz
41  Miki
42  Krish
43  Kumbh

Family Tree Data

ID  UserID  RelativeId  Relation
1   12  13  Spouse
3   12  15  Daughter
4   12  16  Daughter
5   12  17  Son
6   12  18  Son
7   13  12  Spouse
9   13  15  Daughter
10  13  16  Daughter
11  13  17  Son
12  13  18  Son
13  14  15  Spouse
14  14  20  Son
15  14  21  Daughter
16  14  19  Daughter
17  15  12  Father
18  15  13  Mother
19  15  14  Spouse
20  15  16  Sister
21  15  18  Brother
22  15  20  Son
23  15  19  Daughter
24  15  21  Daughter
25  16  12  Father
26  16  13  Mother
27  16  17  Spouse
28  16  14  Brother
29  16  18  Brother
30  16  23  Daughter
31  16  24  Daughter
32  16  25  Son
33  16  26  Son
34  17  16  Spouse
35  17  23  Daughter
36  17  24  Daughter
37  17  25  Son
38  17  26  Son
39  18  12  Father
40  18  13  Mother
41  18  19  Spouse
42  18  27  Daughter
43  18  28  Son
44  18  29  Son
45  18  15  Sister
46  18  16  Sister
47  19  14  Father
48  19  15  Mother
49  19  18  Spouse
50  19  27  Daughter
51  19  28  Son
52  19  29  Son
53  19  20  Brother
54  19  21  Sister
55  20  14  Father
56  20  15  Mother
57  20  21  Sister
58  20  19  Sister
59  20  40  Spouse
60  20  42  Son
61  20  43  Son
62  21  14  Father
63  21  15  Mother
64  21  25  Spouse
65  21  40  Daughter
66  21  41  Son
67  21  20  Brother
68  21  19  Sister
102 22  28  Spouse
103 22  31  Son
69  23  16  Mother
70  23  17  Father
71  23  24  Sister
72  23  25  Brother
73  23  26  Brother
74  24  16  Mother
75  24  17  Father
76  24  23  Sister
77  24  25  Brother
78  24  26  Brother
79  25  16  Mother
80  25  17  Father
81  25  23  Sister
82  25  24  Sister
83  25  26  Brother
84  25  21  Spouse
85  25  40  Daughter
86  25  41  Son
87  26  16  Mother
88  26  17  Father
89  26  23  Sister
90  26  24  Sister
91  26  25  Brother
92  26  27  Spouse
93  26  34  Son
94  26  31  Daughter
95  27  18  Father
96  27  19  Mother
97  27  26  Spouse
98  27  28  Brother
99  27  29  Brother
100 27  34  Son
101 27  35  Daughter
104 28  18  Father
105 28  19  Mother
106 28  27  Sister
107 28  29  Brother
108 28  22  Spouse
109 28  31  Son
110 29  18  Father
111 29  19  Mother
112 29  27  Sister
113 29  28  Brother
114 29  30  Spouse
115 29  32  Son
116 30  29  Spouse
117 30  32  Son
118 31  28  Father
119 31  22  Mother
120 31  34  Spouse
121 31  36  Son
122 32  29  Father
123 32  30  Mother
124 32  33  Spouse
125 32  35  Spouse
126 32  37  Son
127 32  38  Daughter
128 33  32  Spouse
129 33  38  Daughter
130 34  26  Father
131 34  27  Mother
132 34  35  Sister
133 34  31  Spouse
134 34  36  Son
135 35  26  Father
136 35  27  Mother
137 35  32  Spouse
138 35  34  Brother
139 35  37  Son
140 36  34  Father
141 36  31  Father
142 37  35  Mother
143 37  32  Father
144 37  38  Spouse
145 37  39  Daughter
146 38  32  Father
147 38  33  Mother
148 38  37  Spouse
149 38  39  Daughter
150 39  37  Father
151 39  38  Mother
152 40  21  Mother
153 40  25  Father
154 40  41  Brother
155 40  20  Spouse
156 40  42  Son
157 40  43  Son
158 41  21  Mother
159 41  25  Father
160 41  40  Sister
161 42  20  Father
162 42  40  Mother
163 42  43  Brother
164 43  20  Father
165 43  40  Mother
166 43  42  Brother

want to find path to find relation between Rahul(Id=12) and Tejas(Id=32) thanks for the help in advance....

Chart for the above Data:

query provided by astander:

DECLARE @Users TABLE(
        UserID INT,
        UserName VARCHAR(50)
)

DECLARE @FamilyTree TABLE(
        UserID INT,
        RelationID INT,
        Relation VARCHAR(50)
)



INSERT INTO @Users (UserID,UserName) SELECT 1 ,'Rahul'
INSERT INTO @Users (UserID,UserName) SELECT 2 ,'Anjli'
INSERT INTO @Users (UserID,UserName) SELECT 3 ,'Ronak'
INSERT INTO @Users (UserID,UserName) SELECT 4 ,'Rutu'
INSERT INTO @Users (UserID,UserName) SELECT 5 ,'Riya'
INSERT INTO @Users (UserID,UserName) SELECT 6 ,'Rishi'
INSERT INTO @Users (UserID,UserName) SELECT 7 ,'Harry'
INSERT INTO @Users (UserID,UserName) SELECT 8 ,'Dyna'

INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 2 ,'Wife'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 1 ,'Rahul'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 3 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 4 ,'Wife'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 5 ,'Daughter'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 5 ,'Daughter'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 4, 6 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 3, 6 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 1, 7 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 2, 7 ,'Son'
INSERT INTO @FamilyTree (UserID,RelationID,Relation) SELECT 7, 8 ,'Wife'

DECLARE @UserID1 INT,
        @UserID2 INT

SELECT  @UserID1 = 1,
        @UserID2 = 6


DECLARE @Table TABLE(
        cnt INT,
        cpath VARCHAR(50)
)

;WITH fam AS(
        SELECT  *,
                        1 AS CntLevel,
                        CAST('/' + CAST(ft.UserID AS VARCHAR(10)) + '/' AS VARCHAR(50)) AS CLevel
        FROM    @FamilyTree ft 
        WHERE   ft.UserID = @UserID1
        UNION ALL
        SELECT  ft.*,
                        fam.CntLevel + 1 AS CntLevel,
                        CAST(fam.Clevel  + CAST(ft.UserID AS VARCHAR(10)) + '/'AS VARCHAR(50)) AS CLevel
        FROM    @FamilyTree ft INNER JOIN
                        fam ON ft.UserID = fam.RelationID
        WHERE   ft.UserID != @UserID1
)
INSERT INTO @Table(cnt, cpath)
SELECT  CntLevel,
        CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50))
FROM    fam
WHERE   CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('/' + CAST(@UserID1 AS VARCHAR(10)) + '/%')
AND     CAST(fam.Clevel  + CAST(fam.RelationID AS VARCHAR(10)) + '/'AS VARCHAR(50)) LIKE ('%/' + CAST(@UserID2 AS VARCHAR(10))+ '/')

SELECT  DISTINCT 
        ft.* 
FROM    @Table t INNER JOIN
        @FamilyTree ft  ON      PATINDEX('%/' + CAST(ft.UserID AS VARCHAR(10)) + '/%',t.cpath) > 0
                                        AND PATINDEX('%/' + CAST(ft.RelationID AS VARCHAR(10)) + '/%',t.cpath) > 0

解决方案

Well, complicated family. Try this. Work with finally output for customize.

DECLARE @Users TABLE(
        UserID INT,
        UserName VARCHAR(50)
)

DECLARE @FamilyTree TABLE( [ID] int, 
        UserID INT,
         RelativeId INT,
        Relation VARCHAR(50)
)



INSERT INTO @Users
select  12,'Rahul' union
select  13,'Anjali' union
select  14,'Faruk' union
select  15,'Shabina' union
select  16,'Shakira' union
select  17,'Ali' union
select  18,'Ronak' union
select  19,'Dali' union
select  20,'Bali' union
select  21,'Kali' union
select  22,'Katrina' union
select  23,'Sita' union
select  24,'Gita' union
select  25,'Ram' union
select  26,'Shyam' union
select  27,'Suhana' union
select  28,'Suhas' union
select  29,'Raj' union
select  30,'Taslim' union
select  31,'Ritik' union
select  32,'Tejas' union
select  33,'Dipika' union
select  34,'Bush' union
select  35,'Dyna' union
select  36,'Bushiar' union
select  37,'Salman' union
select  38,'Ruksana' union
select  39,'Khushi' union
select  40,'Tazz' union
select  41,'Miki' union
select  42,'Krish' union
select  43,'Kumbh' 




INSERT INTO @FamilyTree ([ID],UserID,RelativeId,Relation)
select  1,12,13,'Spouse' union
select  3,12,15,'Daughter' union
select  4,12,16,'Daughter' union
select  5,12,17,'Son' union
select  6,12,18,'Son' union
select  7,13,12,'Spouse' union
select  9,13,15,'Daughter' union
select  10,13,16,'Daughter' union
select  11,13,17,'Son' union
select  12,13,18,'Son' union
select  13,14,15,'Spouse' union
select  14,14,20,'Son' union
select  15,14,21,'Daughter' union
select  16,14,19,'Daughter' union
select  17,15,12,'Father' union
select  18,15,13,'Mother' union
select  19,15,14,'Spouse' union
select  20,15,16,'Sister' union
select  21,15,18,'Brother' union
select  22,15,20,'Son' union
select  23,15,19,'Daughter' union
select  24,15,21,'Daughter' union
select  25,16,12,'Father' union
select  26,16,13,'Mother' union
select  27,16,17,'Spouse' union
select  28,16,14,'Brother' union
select  29,16,18,'Brother' union
select  30,16,23,'Daughter' union
select  31,16,24,'Daughter' union
select  32,16,25,'Son' union
select  33,16,26,'Son' union
select  34,17,16,'Spouse' union
select  35,17,23,'Daughter' union
select  36,17,24,'Daughter' union
select  37,17,25,'Son' union
select  38,17,26,'Son' union
select  39,18,12,'Father' union
select  40,18,13,'Mother' union
select  41,18,19,'Spouse' union
select  42,18,27,'Daughter' union
select  43,18,28,'Son' union
select  44,18,29,'Son' union
select  45,18,15,'Sister' union
select  46,18,16,'Sister' union
select  47,19,14,'Father' union
select  48,19,15,'Mother' union
select  49,19,18,'Spouse' union
select  50,19,27,'Daughter' union
select  51,19,28,'Son' union
select  52,19,29,'Son' union
select  53,19,20,'Brother' union
select  54,19,21,'Sister' union
select  55,20,14,'Father' union
select  56,20,15,'Mother' union
select  57,20,21,'Sister' union
select  58,20,19,'Sister' union
select  59,20,40,'Spouse' union
select  60,20,42,'Son' union
select  61,20,43,'Son' union
select  62,21,14,'Father' union
select  63,21,15,'Mother' union
select  64,21,25,'Spouse' union
select  65,21,40,'Daughter' union
select  66,21,41,'Son' union
select  67,21,20,'Brother' union
select  68,21,19,'Sister' union
select  102,22,28,'Spouse' union
select  103,22,31,'Son' union
select  69,23,16,'Mother' union
select  70,23,17,'Father' union
select  71,23,24,'Sister' union
select  72,23,25,'Brother' union
select  73,23,26,'Brother' union
select  74,24,16,'Mother' union
select  75,24,17,'Father' union
select  76,24,23,'Sister' union
select  77,24,25,'Brother' union
select  78,24,26,'Brother' union
select  79,25,16,'Mother' union
select  80,25,17,'Father' union
select  81,25,23,'Sister' union
select  82,25,24,'Sister' union
select  83,25,26,'Brother' union
select  84,25,21,'Spouse' union
select  85,25,40,'Daughter' union
select  86,25,41,'Son' union
select  87,26,16,'Mother' union
select  88,26,17,'Father' union
select  89,26,23,'Sister' union
select  90,26,24,'Sister' union
select  91,26,25,'Brother' union
select  92,26,27,'Spouse' union
select  93,26,34,'Son' union
select  94,26,31,'Daughter' union
select  95,27,18,'Father' union
select  96,27,19,'Mother' union
select  97,27,26,'Spouse' union
select  98,27,28,'Brother' union
select  99,27,29,'Brother' union
select  100,27,34,'Son' union
select  101,27,35,'Daughter' union
select  104,28,18,'Father' union
select  105,28,19,'Mother' union
select  106,28,27,'Sister' union
select  107,28,29,'Brother' union
select  108,28,22,'Spouse' union
select  109,28,31,'Son' union
select  110,29,18,'Father' union
select  111,29,19,'Mother' union
select  112,29,27,'Sister' union
select  113,29,28,'Brother' union
select  114,29,30,'Spouse' union
select  115,29,32,'Son' union
select  116,30,29,'Spouse' union
select  117,30,32,'Son' union
select  118,31,28,'Father' union
select  119,31,22,'Mother' union
select  120,31,34,'Spouse' union
select  121,31,36,'Son' union
select  122,32,29,'Father' union
select  123,32,30,'Mother' union
select  124,32,33,'Spouse' union
select  125,32,35,'Spouse' union
select  126,32,37,'Son' union
select  127,32,38,'Daughter' union
select  128,33,32,'Spouse' union
select  129,33,38,'Daughter' union
select  130,34,26,'Father' union
select  131,34,27,'Mother' union
select  132,34,35,'Sister' union
select  133,34,31,'Spouse' union
select  134,34,36,'Son' union
select  135,35,26,'Father' union
select  136,35,27,'Mother' union
select  137,35,32,'Spouse' union
select  138,35,34,'Brother' union
select  139,35,37,'Son' union
select  140,36,34,'Father' union
select  141,36,31,'Father' union
select  142,37,35,'Mother' union
select  143,37,32,'Father' union
select  144,37,38,'Spouse' union
select  145,37,39,'Daughter' union
select  146,38,32,'Father' union
select  147,38,33,'Mother' union
select  148,38,37,'Spouse' union
select  149,38,39,'Daughter' union
select  150,39,37,'Father' union
select  151,39,38,'Mother' union
select  152,40,21,'Mother' union
select  153,40,25,'Father' union
select  154,40,41,'Brother' union
select  155,40,20,'Spouse' union
select  156,40,42,'Son' union
select  157,40,43,'Son' union
select  158,41,21,'Mother' union
select  159,41,25,'Father' union
select  160,41,40,'Sister' union
select  161,42,20,'Father' union
select  162,42,40,'Mother' union
select  163,42,43,'Brother' union
select  164,43,20,'Father' union
select  165,43,40,'Mother' union
select  166,43,42,'Brother'  




DECLARE @UserID1 INT,
        @UserID2 INT

SELECT  @UserID1 = 12,
        @UserID2 = 32

--descendants of user1
;with famDes
as
(
    select * , 
        cast(ltrim(userid)  as varchar(max)) as [path],
        cast(relation  as varchar(max)) as [path2]
    from @FamilyTree
    where UserID=@UserID1 and Relation in ('Son','Daughter')

    union all
    select ft.*, 
        cast([path]+'.'+ltrim(ft.userid) as varchar(max)),
        cast([path2]+'.'+ft.relation  as varchar(max))
    from @FamilyTree ft
    inner join famDes on ft.UserID=famDes.RelativeId and ft.Relation in ('Son','Daughter')

),
--ascendants of user1
famAsc
as
(
    select * , 
        cast(ltrim(userid)  as varchar(max)) as [path],
        cast(relation  as varchar(max)) as [path2]
    from @FamilyTree
    where UserID=@UserID1 and Relation in ('Father','Mother')

    union all
    select ft.*, 
        cast([path]+'.'+ltrim(ft.userid) as varchar(max)),
        cast([path2]+'.'+ft.relation  as varchar(max))
    from @FamilyTree ft
    inner join famAsc on ft.UserID=famAsc.RelativeId and ft.Relation in ('Father','Mother')

)
select 
    * 
from 
    famDes
where  
    relativeID=@UserID2 
union all
select 
    * 
from 
    famAsc
where  
    relativeID=@UserID2 
union all
--if user1 and user2 are brothers or Spouses
select 
    * , 
    cast(ltrim(userid)  as varchar(max)) as [path],
    cast(relation  as varchar(max)) as [path2]
    from @FamilyTree
where 
    UserID in (@UserID1,@UserID2) 
    and Relation in ('Brother','Sister','Spouse')
    and relativeID in (@UserID2 ,@UserID1 )

这篇关于Sql递归查询来识别家谱的2个用户之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

Sql递归查询来识别家谱的2个用户之间的关系 [英] Sql Recursive query to identify relation between 2 users of family tree

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

Sql递归查询来识别家谱的2个用户之间的关系 [英] Sql Recursive query to identify relation between 2 users of family tree

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭