父主键为主键的SQL同表查询中递归父/子

Question

我见过很多关于如何实现递归查询的示例，其中父查询和子查询在同一个表中，但在示例中，子查询有父查询，而当父查询有子查询时，我需要的正好相反。我想以递归模式获取所有子对象，就像在图像中一样。

​

在图像中，您可以看到，我有一个id为1的父对象，它有一个id为2的子对象。子对象2也是一个具有id为3的子对象的父对象，依此类推。我不知道如何创建递归查询来从父对象中获取所有孩子。您可以访问下一个链接来执行sql online：http://www.sqlfiddle.com/#!18/dbed2/1

Answer 1

这会产生您想要的结果：

with cte as (
      select idchild, idparent,
             convert(varchar(max), idchild) as children
      from family f
      where not exists (select 1 from family f2 where f2.idparent = f.idchild)
      union all
      select f.idchild, f.idparent,
             concat(f.idchild, ',', cte.children)
      from cte join
           family f
           on cte.idparent = f.idchild
     )
 select *
 from cte
 order by idchild;

Here就是SQL Fiddle。

Answer 2

这就是了：

with
n as (
  select idparent, idchild, 1 as lvl, 
    cast(concat('', idchild) as varchar(255)) as children from family
union all
  select n.idparent, f.idchild, lvl + 1, 
    cast(concat(children, ',', f.idchild) as varchar(255))
  from n
  join family f on f.idparent = n.idchild
)
select n.idparent, f.idchild, n.children
from n
join (
  select idparent, max(lvl) as maxlvl from n group by idparent
) m on n.idparent = m.idparent and n.lvl = m.maxlvl
join family f on f.idparent = n.idparent
order by n.idparent

参见SQL Fiddle。

Answer 3

如果您使用的是SQL Server 2017或更高版本，则可以使用以下各项：

WITH CTE
AS (SELECT *
    FROM dbo.Table_1
    UNION ALL
    SELECT Child.idParent,
           Parent.idChild
    FROM CTE AS Parent
        INNER JOIN dbo.Table_1 AS Child
            ON Parent.idParent = Child.idChild)
SELECT CTE.idParent,
       STRING_AGG(CTE.idChild, ', ') AS Childs
FROM CTE
GROUP BY CTE.idParent;

但是，如果您有较旧的版本，请使用以下内容：

WITH CTE
AS (SELECT *
    FROM dbo.Table_1
    UNION ALL
    SELECT Child.idParent,
           Parent.idChild
    FROM CTE AS Parent
        INNER JOIN dbo.Table_1 AS Child
            ON Parent.idParent = Child.idChild)
SELECT DISTINCT
       B.idParent,
       STUFF(
       (
           SELECT ',' + CONVERT(VARCHAR(10), CTE.idChild)
           FROM CTE
           WHERE B.idParent = CTE.idParent
           ORDER BY CTE.idChild
           FOR XML PATH('')
       ),
       1,
       1,
       ''
            ) AS Childs
FROM CTE AS B