在家庭关系表中添加家庭ID列

Question

我的资料来源数据库中有一张表格，把一个人与他的妻子/丈夫及其子女(如果他们有)联系起来。

与子女的关系可以通过妻子或丈夫进行，但只有一个成年人与子女之间有关系，而夫妻之间只有一种关系。与配偶有关系的不一定是同一个成年人，而是与孩子/孩子有关系的成年人。

我想要创造一个输出，其中我有每个人和一个独特的身份，他们的家庭。但我似乎无法思考如何得到这个结果。

下面是我的源数据样本。

+--------+------+
| FromID | ToID |
+--------+------+
|      1 |    2 |
|      2 |    3 |
|      2 |    4 |
|      5 |    6 |
|      6 |    7 |
|      8 |    9 |
+--------+------+

   CREATE TABLE [dbo].[Relations](
            [FromID] [int] NULL,
            [ToID] [int] NULL
        ) 
        INSERT [dbo].[Relations] ([FromID], [ToID]) VALUES (1, 2)
        INSERT [dbo].[Relations] ([FromID], [ToID]) VALUES (2, 3)
        INSERT [dbo].[Relations] ([FromID], [ToID]) VALUES (2, 4)
        INSERT [dbo].[Relations] ([FromID], [ToID]) VALUES (5, 6)
        INSERT [dbo].[Relations] ([FromID], [ToID]) VALUES (6, 7)
        INSERT [dbo].[Relations] ([FromID], [ToID]) VALUES (8, 9)

在本示例数据中：

• 1人与2人结婚，2人有2个子女(3，4人)(4人的家庭)
• 5人与6人结婚，6人有1名子女(7人)(3人家庭)
• 人8与人9结婚，无子女(2口之家)

以下是预期的结果：

+----------+----------+
| PersonID | FamilyID |
+----------+----------+
|        1 |        1 |
|        2 |        1 |
|        3 |        1 |
|        4 |        1 |
|        5 |        2 |
|        6 |        2 |
|        7 |        2 |
|        8 |        3 |
|        9 |        3 |
+----------+----------+

Answer 1

以下是实现这一结果的另一种方法：

SELECT DISTINCT
  COALESCE(h.husband, d.dependents) AS id,
  COALESCE(d.familyid, h.familyid) AS familyid
FROM (SELECT
            r.ToID AS dependents,
            DENSE_RANK() OVER (ORDER BY COALESCE(r2.Fromid, r.Fromid)) AS familyid
      FROM Relations r
      LEFT JOIN Relations r2
        ON r.[FromID] = r2.ToID) d
FULL JOIN (SELECT
              COALESCE(r2.Fromid, r.Fromid) AS husband,
              DENSE_RANK() OVER (ORDER BY COALESCE(r2.Fromid, r.Fromid)) AS familyid
           FROM Relations r
           LEFT JOIN Relations r2
             ON r.[FromID] = r2.ToID) h
  ON d.dependents = h.husband

Answer 2

首先，你需要找出每个家庭的丈夫，并分配和身份证。

SQL演示

WITH husband as (      
    SELECT R1.[FromID] as [ID],
           ROW_NUMBER() OVER (ORDER BY R1.[FromID]) as rn
    FROM [Relations] R1
    LEFT JOIN [Relations] R2
      ON R1.[FromID] = R2.[ToID]
    WHERE R2.[FromID] IS NULL
)
SELECT *
FROM husband

输出

​

​

然后，只需使用递归查询获取ID的所有家庭成员

https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

编辑：

因为您的树只有两个层次深度，所以可以使用JOINS来完成，但是如果您想处理单亲家庭，我可以看到问题。

SQL演示

WITH husbands as (      
    SELECT  ROW_NUMBER() OVER (ORDER BY R1.[FromID]) as [familyID],
            R1.[FromID] as [ID]           
    FROM [Relations] R1
    LEFT JOIN [Relations] R2
      ON R1.[FromID] = R2.[ToID]
    WHERE R2.[FromID] IS NULL
), wifes as (
    SELECT H.[familyID], R1.[ToID] as [ID]
    FROM husbands H
    JOIN Relations R1
      ON H.[ID] = R1.[FromID] 
), childrens as (
    SELECT H.[familyID], R2.[ToID] as [ID]
    FROM husbands H
    JOIN Relations R1
      ON H.[ID] = R1.[FromID] 
    JOIN Relations R2
      ON R1.[ToID] = R2.[FromID]     
)    
SELECT * FROM husbands UNION 
SELECT * FROM wifes UNION 
SELECT * FROM childrens

输出

​

​