T-SQL get root node in hierarchy(T-SQL 获取层次结构中的根节点)
问题描述
所以我有两个这样结构的表:
So I have two tables structured like so:
如果我有两个节点,比如说 1 和 2.我想要一个它们的根节点的列表.在这种情况下,它将是 1、4 和 7.我如何编写查询来获取该信息?
If I have two nodes, lets say 1 and 2. I want a list of their root nodes. In this case it would be 1, 4, and 7. How can I write a query to get me that information ?
我尝试编写它,但遇到了一个问题,即由于某种未知原因,我无法在 CTE 的递归部分使用 LEFT 连接.如果允许我执行 LEFT JOIN,这里是可行的查询.
I took a stab at writing it but ran into the issue that I can't use a LEFT join in the recursive part of a CTE for some unknown reason. Here is the query that would work if I was allowed to do a LEFT JOIN.
有什么方法可以重组查询以获得我想要的?我无法重组数据,我真的希望远离临时表或不得不做任何昂贵的事情.
Is there a way I can restructure the query to get what I want ? I can't restructure the data and I would really prefer to stay away from temporary tables or having to do anything expensive.
谢谢,劳尔
推荐答案
将 LEFT JOIN 移出 CTE 怎么样?
How about moving the LEFT JOIN out of the CTE?
结果集为 1、4、7.
The result set is 1, 4, 7.
这篇关于T-SQL 获取层次结构中的根节点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!