T-SQL 查询:获取父节点的子节点 [英] T-SQL Query : Getting Child nodes of a parent

问题描述

我有一个具有以下架构的表:

I have a table with the following schema :

ID , CatID, ParentCatID, SiteID

我想获取属于根类别的所有站点(意味着它们的 ParentCatID = 0)及其所有后代.

I want to get all the sites that belong to the categories that are the roots ( means their ParentCatID = 0) and all their descendants.

例如:

ID , CatID, ParentCatID, SiteID
--------------------------------
1  , 2    , 0          , 3
1  , 4    , 2          , 6
1  , 5    , 4          , 7

在这个例子中，CatID 2 是 4 的父级，4 是 5 的父级.

In this example CatID 2 is the parent of 4 and 4 is the parent of 5.

如何获取属于根类别及其所有后代的所有 SiteID.

How can I get all the SiteIDs that belongs to the root category and all its descendants.

推荐答案

使用 递归SQL Server 2005+ 支持的公共表表达式:

WITH hierarchy AS (
  SELECT yt.id, 
         yt.catid,
         yt.parentcatid,
         yt.siteid
    FROM YOUR_TABLE yt
   WHERE yt.parentcatid = 0
  UNION ALL
  SELECT yt.id, 
         yt.catid,
         yt.parentcatid,
         yt.siteid
    FROM YOUR_TABLE yt
    JOIN hierarchy h ON h.catid = yt.catid)
SELECT t.*
  FROM hierarchy t
OPTION (maxrecursion 1000)

如果你得到:

语句终止.最大递归100在语句完成前已用完

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

默认为 100 次递归.可以通过maxrecursion 选项设置最大递归次数，最大为 32767.

The default is 100 recursions. The maximum number of recursions can be set via the maxrecursion option, up to a maximum of 32767.

这篇关于T-SQL 查询:获取父节点的子节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！