sql-server - 无需循环遍历和获取图中的节点

Question

我有一张个人表，其中保存了一些个人信息。如下表所示。

+----+------+----------+----------+--------+
| ID | name | motherID | fatherID |  sex   |
+----+------+----------+----------+--------+
|  1 | A    | NULL     | NULL     | male   |
|  2 | B    | NULL     | NULL     | female |
|  3 | C    | 1        | 2        | male   |
|  4 | X    | NULL     | NULL     | male   |
|  5 | Y    | NULL     | NULL     | female |
|  6 | Z    | 5        | 4        | female |
|  7 | T    | NULL     | NULL     | female |
+----+------+----------+----------+--------+

我也保持人与人之间的婚姻关系。喜欢:

+-----------+--------+
| HusbandID | WifeID |
+-----------+--------+
|         1 |      2 |
|         4 |      5 |
|         1 |      5 |
|         3 |      6 |
+-----------+--------+

有了这些信息我们就可以想象出关系图了。如下所示；

问题是:我如何通过提供任何人的 ID 来获得所有关联的人。

例如;

• 当我给ID=1时，它应该返回给我1,2,3,4,5,6。(顺序不重要)
• 同样，当我输入 ID=6 时，它应该返回给我 1,2,3,4,5,6。(顺序不重要)
• 同样，当我给 ID=7 时，它应该返回给我 7。

请注意:人物节点的关系(边)可能在图的任何地方都有循环。上面的示例显示了我的一小部分数据。我是说; person 和 marriage 表可能包含数千行，我们不知道哪里会出现循环。

类似的问题在 :

PostgreSQL SQL query for traversing an entire undirected graph and returning all edges found http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118319

但我无法编写有效的 SQL。提前致谢。 我正在使用 SQL Server。

Answer 1

在 SQL Server 2017 和 Azure SQL DB 中，您可以使用 new graph database capabilities和新的 MATCH 子句来回答这样的查询，例如

SELECT FORMATMESSAGE ( 'Person %s (%i) has mother %s (%i) and father %s (%i).', person.userName, person.personId, mother.userName, mother.personId, father.userName, father.personId ) msg
FROM dbo.persons person, dbo.relationship hasMother, dbo.persons mother, dbo.relationship hasFather, dbo.persons father
WHERE hasMother.relationshipType = 'mother'
  AND hasFather.relationshipType = 'father'
  AND MATCH ( father-(hasFather)->person<-(hasMother)-mother );

我的结果:

完整脚本可用here .

对于您的具体问题，当前版本不包括传递闭包(循环图 n 次的能力)或多态性(找到图中的任何节点)并且回答这些查询可能涉及循环、递归 CTE 或临时表。我在我的示例脚本中尝试过此操作，它适用于您的示例数据，但这只是一个示例 - 我不是 100% 它可以与其他示例数据一起使用。