我有一个包含三个表的数据库：
关系类型表
id relation_title
----------------------
1 father
2 mother
3 son
4 daughter
5 husband
6 wife
用户表
id user_name
----------------------
1 amr ali
2 ali saad
3 amal samir
4 sara said
5 said mohammed
6 mona ashraf
关系表
id first_id second_id relation_type_id
---------------------------------------------------------
1 1 2 3
2 2 1 1
3 1 3 3
4 3 1 2
5 1 4 5
6 4 1 6
7 4 5 4
8 5 4 1
9 5 6 5
10 6 5 6
当我运行查询时，我得到以下结果：
SELECT u1.user_name,
u2.user_name,
r_t.relation_title
FROM relationships r
JOIN relation_type r_t ON r.relation_type_id = r_t.id
JOIN users u1 ON r.first_id = u1.id
JOIN users u2 ON r.second_id = u2.id
WHERE first_id = 1
================================================
user_name user_name relation_title
------------------------------------------------
amr ali ali saad son
amr ali amal samir son
amr ali sara said husband
当我想画出两个与关系没有直接关系的人之间的关系链时，我想得到的是什么，比如"；amr ali"以及"；莫娜·阿什拉夫。。。如何通过SQL查询获得下图
================================================
user_name user_name relation_title
------------------------------------------------
amr ali sara said husband
sara said said mohammed daughter
said mohammed mona ashraf husband
你可以尝试使用递归CTE，你必须在CTE的第一部分进行限制以避免递归循环，第二部分是尝试限制关系，这样你就不会同时得到儿子和母亲：
SQL Fiddle
MS SQL Server 2017架构设置:
create table relation_type
(
ID int PRIMARY KEY,
Relation_Title varchar(20)
);
CREATE TABLE users
(
ID int PRIMARY KEY,
user_name varchar(20)
);
CREATE TABLE relationships
(
ID int PRIMARY KEY,
first_id int,
second_id int,
relation_type_id int
);
INSERT INTO relation_type
VALUES (1, 'father'),
(2, 'mother'),
(3, 'son'),
(4, 'daughter'),
(5, 'husband'),
(6, 'wife');
INSERT INTO users
VALUES (1, 'amr ali'),
(2, 'ali saad'),
(3, 'amal samir'),
(4, 'sara said'),
(5, 'said mohammed'),
(6, 'mona ashraf');
INSERT INTO relationships
VALUES (1,1,2,3),
(2, 2, 1, 1),
(3, 1, 3, 3),
(4, 3, 1, 2),
(5, 1, 4, 5),
(6, 4, 1, 6),
(7, 4, 5, 4),
(8, 5, 4, 1),
(9, 5, 6, 5),
(10, 6, 5, 6);
查询1：
WITH CTE
AS
(
SELECT u1.user_name first_user_name,
u2.user_name second_user_name,
r_t.relation_title,
r.first_id,
r.second_id,
0 as lvl
FROM relationships r
JOIN relation_type r_t ON r.relation_type_id = r_t.id
JOIN users u1 ON r.first_id = u1.id
JOIN users u2 ON r.second_id = u2.id
WHERE r.first_id = 1
UNION ALL
SELECT u1.user_name first_user_name,
u2.user_name second_user_name,
r_t.relation_title,
r.first_id,
r.second_id,
c.lvl + 1
FROM relationships r
JOIN relation_type r_t ON r.relation_type_id = r_t.id
JOIN users u1 ON r.first_id = u1.id
JOIN users u2 ON r.second_id = u2.id
JOIN CTE C on C.second_id = r.first_Id
WHERE
((C.Relation_Title IN ('father','mother')
AND r_t.Relation_Title not in ('son','daughter')) OR
(C.Relation_Title IN ('son','daughter')
AND r_t.Relation_Title not in ('father','mother')) OR
(C.Relation_Title = 'husband'
AND r_t.Relation_Title <> 'wife') OR
(C.Relation_Title = 'wife'
AND r_t.Relation_Title <> 'husband'))
)
SELECT *
FROM CTE
结果：
| first_user_name | second_user_name | relation_title | first_id | second_id | lvl |
|-----------------|------------------|----------------|----------|-----------|-----|
| amr ali | ali saad | son | 1 | 2 | 0 |
| amr ali | amal samir | son | 1 | 3 | 0 |
| amr ali | sara said | husband | 1 | 4 | 0 |
| sara said | said mohammed | daughter | 4 | 5 | 1 |
| said mohammed | mona ashraf | husband | 5 | 6 | 2 |