选择同一表关系 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           |
+-----------+----+---+----------+------------+

更新行后，我需要检查元素的父键，以不允许元素是自给自足之类的..

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

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