选择同一表关系中的所有父级或子级SQL Server [英] Select all parents or children in same table relation SQL Server

问题描述

SQL开发人员，我有一个计划不周的数据库作为任务，以学习有关SQL Server 2012的很多知识.

SQL developers, I have a badly planned database as task to learn a lot about SQL Server 2012.

因此，有表格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           |
+-----------+----+---+----------+------------+

更新行后，我需要检查element的父键，以不允许element自怨自艾.

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.

问题是:

1. 如何选择一个DIST元素的所有父母+祖父母+等等"?

1. How can i select all "parent + grandparent + etc" of one element of DIST?

我如何选择DIST的一个元素的所有儿子+孙子+等等"?

How can i selects all "sons + grandsons + etc" of one element of DIST?

我阅读了有关CTE的解决方案的信息，但是我没有任何基础，甚至不知道那时如何使用CTE.

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.

请帮助！

谢谢.

推荐答案

我遇到了这个问题，我通过这种方式解决了问题

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.我的英语不好.

PS.My English is not good.

这篇关于选择同一表关系中的所有父级或子级SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！