选择同一表关系 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.
问题是:
如何选择 DIST 的一个元素的所有父级 + 祖父级 + 等"?
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.我的英文不好.
相关文章