如何在 SQL Server 中进行上递归自联接? [英] How to do an upper recursive self-join in SQL Server?

问题描述

如何在 SQL Server 中进行递归自联接?我有一张这样的桌子:

<前>表ID |家长ID1 |空值2 |13 |14 |35 |空值6 |47 |6

我想根据给定的 TableID 获得以下结果，以获取与 TableID 相关的所有 ParentsID，假设我想要获取 TableID = 6 的所有父项:

<前>表ID6431

我被困在这个问题上，我不知道如何在 SQL 查询中获得结果...希望能告诉我获取之前数据的SQL Query

解决方案

应该是

<代码>;WITH MyQuery (TableID, ParentID, Level) AS(SELECT M.TableID, M.ParentID, 0 AS Level从 MyTable MWHERE M.TableID = 6 -- 这是查询开始的行号联合所有SELECT M.TableID, M.ParentID, Q.Level + 1从 MyTable MINNER JOIN MyQuery Q ON M.TableID = Q.ParentID)SELECT * FROM MyQuery;

正如 Byers 所写，这是一个使用公用表表达式的递归查询

Level 列是无用的(它不是无用的无用".它对您提出的要求没有用)，我添加了它，因为它经常插入到这些递归查询中.如果你不需要它，从它出现的 3 个地方删除它.

反过来做Level似乎要复杂得多(所以曾祖父是0级，他的孩子是1级......)

请注意，此代码适用于 SQL Server >= 2005

How can I do a recursive self-join in SQL Server ? I have a table like this:

TableID | ParentID
   1    |     NULL
   2    |        1
   3    |        1
   4    |        3
   5    |     NULL
   6    |        4
   7    |        6

I want to get the following results based on given TableID to get all the ParentsID related to the TableID, let's say I want to get all the parents for the TableID = 6 :

TableID
   6
   4
   3
   1

I'm stuck on this and i don't know how to get the result in SQL Query ... Hope to tell me the SQL Query to get the previous data

解决方案

It should be

; WITH MyQuery (TableID, ParentID, Level) AS
(
    SELECT M.TableID, M.ParentID, 0 AS Level 
        FROM MyTable M 
        WHERE M.TableID = 6 -- Here it's the row number where the query starts

    UNION ALL

    SELECT M.TableID, M.ParentID, Q.Level + 1 
        FROM MyTable M 
        INNER JOIN MyQuery Q ON M.TableID = Q.ParentID
)

SELECT * FROM MyQuery;

and as written by Byers, it's a Recursive Queries Using Common Table Expressions

The Level column is useless (it isn't "useless useless". It's useless for what you asked), I have added it because it's quite often inserted in these recursive queries. If you don't need it, delete it from the 3 places it appears.

It seems to be much more complex to do the Level in reverse (so that the grand-grand father is level 0, his childs are level 1...)

Note that this code will work with SQL Server >= 2005

这篇关于如何在 SQL Server 中进行上递归自联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！