SQL Server查询以查找子代的父名称 [英] SQL Server query to find parent names of child

问题描述

我有一个像这样的表

ID  Name    Mother  Father
1   Sue     NULL    NULL
2   Ed      NULL    NULL
3   Emma    1       2
4   Jack    1       2
5   Jane    NULL    NULL
6   Bonnie  5       4
7   Bill    5       4

我需要如下输出

ID  Name    Mother  Father
3   Emma    sue     ed
4   jack    sue     ed
6   bonnie  jane    jack
7   bill    jane    jack

我尝试使用join n cte编写查询，但是无法提出逻辑，有人可以帮帮我吗

i have tried writing query with join n cte but couldnt come up with the logic , can someone please help me out

推荐答案

SELECT t.ID, t.Name, m.Name, f.Name
FROM your_table t
INNER JOIN your_table m ON m.ID = t.Mother
INNER JOIN your_table f ON f.ID = t.Father

如果要包括没有Mother和/或Father节点的记录，请使用LEFT JOIN:

Use LEFT JOIN if you want to include records without Mother and/or Father nodes:

SELECT t.ID, t.Name, ISNULL(m.Name, 'Orphan') Mother, ISNULL(f.Name, 'Orphan') Father
FROM your_table t
LEFT JOIN your_table m ON m.ID = t.Mother
LEFT JOIN your_table f ON f.ID = t.Father

这篇关于SQL Server查询以查找子代的父名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！