选择同一表关系 SQL Server 中的所有父级或子级
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21230240/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select all parents or children in same table relation SQL Server
提问by Khronos
SQL developers, I have a badly planned database as task to learn a lot about SQL Server 2012.
SQL 开发人员，我有一个计划不当的数据库作为任务来学习很多关于 SQL Server 2012 的知识。
SO, there is the table Elem:
所以，有表Elem：
+-----------+----+---+----------+------------+
|VERSION(PK)|NAME|KEY|PARENT_KEY|DIST_KEY(FK)|
+-----------+----+---+----------+------------+
|1 |a |12 |NULL |1 |
+-----------+----+---+----------+------------+
|2 |b |13 |12 |1 |
+-----------+----+---+----------+------------+
|3 |c |14 |13 |1 |
+-----------+----+---+----------+------------+
|4 |d |15 |12 |1 |
+-----------+----+---+----------+------------+
|5 |e |16 |NULL |1 |
+-----------+----+---+----------+------------+
|6 |e |17 |NULL |2 |
+-----------+----+---+----------+------------+
After update the row I need to check parent key of element to not allow element to be self-granny or something..
更新行后，我需要检查元素的父键以不允许元素成为自奶奶之类的东西..
And when I delete the row I need to delete all children and children of children, etc.
当我删除该行时，我需要删除所有孩子和孩子的孩子等。
Questions are:
问题是：
How can i select all "parent + grandparent + etc" of one element of DIST?
How can i selects all "sons + grandsons + etc" of one element of DIST?
如何选择 DIST 的一个元素的所有“父级 + 祖父级 + 等”？
如何选择 DIST 的一个元素的所有“儿子 + 孙子 + 等”？
I read about solutions with CTE, but I have no root of elements and I can't even understand how I can use CTE then.
我阅读了关于 CTE 的解决方案，但我没有元素的根，我什至无法理解我如何使用 CTE。
Please, help!
请帮忙！
Thanks.
谢谢。
回答by HunkHui
I have met this problem，I resolved problem by this way
我遇到了这个问题，我是通过这种方式解决的
--all "parent + grandparent + etc" @childID Replaced with the ID you need
with tbParent as
(
select * from Elem where [KEY]=@childID
union all
select Elem.* from Elem join tbParent on Elem.[KEY]=tbParent.PARENT_KEY
)
SELECT * FROM tbParent
--all "sons + grandsons + etc" @parentID Replaced with the ID you need
with tbsons as
(
select * from Elem where [KEY]=@parentID
union all
select Elem.* from Elem join tbsons on Elem.PARENT_KEY=tbsons.[KEY]
)
SELECT * FROM tbsons
PS.My English is not good.
PS.我的英文不好。
回答by Thorsten Kettner
here is a recursive query giving you both all ancestors and all descendants of an element. Use these together or separate according to the situation. Replace the where clauses to get the desired record. In this example I am looking for key 13 (this is the element with name = b) and find its ancestor 12/a and its descendant 14/c.
这是一个递归查询，为您提供元素的所有祖先和所有后代。根据情况将它们一起使用或分开使用。替换 where 子句以获得所需的记录。在这个例子中，我正在寻找键 13（这是名称为 b 的元素）并找到它的祖先 12/a 和它的后代 14/c。
with all_ancestors(relation, version, name, elem_key, parent_key, dist_key)
as
(
-- the record itself
select 'self ' as relation, self.version, self.name, self.elem_key, self.parent_key, self.dist_key
from elem self
where elem_key = 13
union all
-- all its ancestors found recursively
select 'ancestor ' as relation, parent.version, parent.name, parent.elem_key, parent.parent_key, parent.dist_key
from elem parent
join all_ancestors child on parent.elem_key = child.parent_key
)
, all_descendants(relation, version, name, elem_key, parent_key, dist_key)
as
(
-- the record itself
select 'self ' as relation, self.version, self.name, self.elem_key, self.parent_key, self.dist_key
from elem self
where elem_key = 13
union all
-- all its descendants found recursively
select 'descendant' as relation, child.version, child.name, child.elem_key, child.parent_key, child.dist_key
from elem child
join all_descendants parent on parent.elem_key = child.parent_key
)
select * from all_ancestors
union
select * from all_descendants
order by elem_key
;
Here is the SQL fiddle: http://sqlfiddle.com/#!6/617ee/28.
这是 SQL 小提琴：http: //sqlfiddle.com/#!6/ 617ee/28 。
回答by Suraj Shrestha
I've created a function for finding the parents of a specific child where you have to pass the Id of the child.
我创建了一个函数，用于查找特定孩子的父母，您必须在其中传递孩子的 Id。
This will return the list of parents as a comma separated string. Try this if it works for you.
这将返回父母列表作为逗号分隔的字符串。如果它适合你，试试这个。
I'm assuming that the parent_key with null valueis root.
我假设parent_key with null value是根。
CREATE FUNCTION checkParent(@childId INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @parentId VARCHAR(MAX) = NULL
DECLARE @parentKey INT = null
SET @parentId = (SELECT parent_key FROM Elem WHERE [KEY] = @childId)
WHILE(@parentKey IS NOT NULL)
begin
SET @parentId = @parentId + ', ' + (SELECT parent_key FROM Elem WHERE [KEY] = @parentId)
SET @parentKey = (SELECT parent_key FROM Elem WHERE [KEY] = @parentId)
END
RETURN @parentId
END
GO
回答by Ján Srni?ek
I dont think it can be done in one select in whole case ,so that you can select all parents,grandparents ,... . One way how to do it is to join elem table to herself ,and thet it depends on how many levels of join you do , that level of childs,grandchilds will you get.
我不认为它可以在整个案例中进行一次选择，以便您可以选择所有父母，祖父母......。如何做到这一点的一种方法是将 elem 表加入到她自己，这取决于您进行了多少级别的加入，您将获得该级别的孩子，孙子。
The solution can be somethink like this(for the second case )
解决方案可以是这样的（对于第二种情况）
this will select you all parents,childs and grandchilds
这将选择你所有的父母、孩子和孙子
Select
parent.key as parent_key,
child.key as child_key,
grandchild.key as grandchild_key
from elem parent
join elem child on (elem.key=child.parentkey)
join elem grandchild on (child.key=grandchild.parentkey)
where parent.parentkey is null; -- this make you sure that first level will be parents
solution for the first case is just that you will connect tables not in style of 'key=parentkey' but oposite 'parentkey=key'.
第一种情况的解决方案只是您将连接表的样式不是“key=parentkey”，而是相反的“parentkey=key”。
