65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server中管理层次或父子关系行的SQL查询

<b>Dipon<font color="orange">Roy</font></b>
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.86/5 (46投票s)

2014年9月16日

CPOL

3分钟阅读

viewsIcon

520766

downloadIcon

2270

一些查询，用于查找每行的代数、所有可能的子级、所有可能的父级，以及管理递归

引言

在此文章中，我们将尝试管理 SQL Server 中特定表的具有分层关系或父子关系的数据。 我们的重点将是：

  • 显示每行的代数
  • 查找特定行的所有可能的父级
  • 查找特定行的所有可能的子级
  • 在一个列中显示所有可能的父级，并使用分隔符
  • 在一个列中显示所有可能的子级，并使用分隔符

背景

让我们假设

  • 一行只能有一个父级，或者没有父级时为 Null
  • 至少有一行没有父级（parentId 为 null）
  • 并且至少有一行没有任何子级

这是我们的 table 模式

/*drop the tbl*/
--DROP TABLE UserType

/*create the tbl*/
CREATE TABLE UserType(
    Id BIGINT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    ParentId BIGINT NULL    
)

让我们用数据填充 table

/*insert data into table*/
DECLARE @maxCount BIGINT,
        @count BIGINT,
        @parentId BIGINT;        
SET @maxCount = 10;        /*change to input more*/
SET @count = 1;

WHILE @count <= @maxCount
BEGIN
    If @count = 1
        SET @parentId = NULL;
    ELSE
        SET @parentId = @count - 1;
        
    INSERT INTO UserType(Id, Name, ParentId)
        VALUES (@count, 'User_' + CONVERT(VARCHAR(400), @count), @parentId)
    SET @count = @count + 1;
END

因此，我们填充后的 table 将如下所示

/*show inserted datas*/
SELECT *
    FROM UserType;

在线查看 http://www.sqlfiddle.com/#!3/f50a6/1

现在，如何使用 SQL 查找特定行的所有这些代数、父级或子级 …!!!

答案是使用递归。 但是要使用此递归，我们需要称为 CTE（公共表表达式）的东西，或者在 SQL 中的语法“WITH”。 如果我们对此没有任何概念，我们可以从链接或 Google 开始搜索片刻。

因此，让我们从非常基本的东西开始。

常规连接

基于条件将 table 与自身连接，其中一个 parentId 等于另一个的 Id。

/*regular join to get detail*/    
SELECT ChildUserType.Id, ChildUserType.Name, ParentUserType.Id, ParentUserType.Name
    FROM UserType AS ChildUserType
    LEFT JOIN UserType AS ParentUserType ON ChildUserType.ParentId = ParentUserType.Id;

在线查看 http://www.sqlfiddle.com/#!3/f50a6/2

行生成

该过程类似

  • 所有没有父级的行（NULL），为其分配代数 0
  • 查找父级属于代数 0 的行，并为其自身分配增加的代数
  • 执行直到递归完成
/*row generations*/
WITH Hierarchy(ChildId, ChildName, Generation, ParentId)
AS
(
    SELECT Id, Name, 0, ParentId
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL        
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.Generation + 1, Parent.ChildId
        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
    OPTION(MAXRECURSION 32767)

在线查看 http://www.sqlfiddle.com/#!3/f50a6/3

所有可能的父级

查看 http://stackoverflow.com/a/21233413/2948523

在这里，我们尝试查找 Id = 5 的行的所有可能的父级。

  • 首先选择 Id = 5 的行
  • 查找其他行，其中其 id 等于先前选择的 ParentId
  • 并继续减少
--all possible parents of @id
DECLARE @id BIGINT;
SET @id = 5;
WITH tblParent AS
(
    SELECT *
        FROM UserType WHERE Id = @id
    UNION ALL
    SELECT UserType.*
        FROM UserType  JOIN tblParent  ON UserType.Id = tblParent.ParentId
)
SELECT * FROM  tblParent
    WHERE Id <> @id
OPTION(MAXRECURSION 32767)

在线查看 http://www.sqlfiddle.com/#!3/f50a6/5

所有可能的子级

查看 http://stackoverflow.com/a/21233413/2948523

在这里，我们尝试查找 Id = 5 的行的所有可能的子级。

  • 首先选择 Id = 5 的行
  • 查找其他行，其中其 ParentId 等于先前选择的 Id
  • 并继续减少
-- all possible childs of @userTypeId
DECLARE @userTypeId BIGINT;
SET @userTypeId = 5;
WITH tblChild AS
(
    SELECT *
        FROM UserType WHERE ParentId = @userTypeId
    UNION ALL
    SELECT UserType.* FROM UserType  JOIN tblChild  ON UserType.ParentId = tblChild.Id
)
SELECT *
    FROM tblChild
OPTION(MAXRECURSION 32767)

在线查看 http://www.sqlfiddle.com/#!3/f50a6/6

一列中的所有可能的父级

在这里，我们在列中显示所有可能的父级 Id，并使用特定分隔符“.”

/*row possible parents in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL    
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
    CAST(CASE WHEN Parent.Parents = ''
        THEN(CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
        ELSE(Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)

在线查看 http://www.sqlfiddle.com/#!3/f50a6/7

一列中的所有可能的子级

在这里，我们在列中显示所有可能的子级的 Id，并使用特定分隔符“.”

/*row possible childs in a column*/
WITH Hierarchy(ChildId, ChildName, ParentId, Childs)
AS
(
    SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
        FROM UserType AS LastGeneration
        WHERE Id NOT IN (SELECT COALESCE(ParentId, 0) FROM UserType)     
    UNION ALL
    SELECT PrevGeneration.Id, PrevGeneration.Name, PrevGeneration.ParentId,
    CAST(CASE WHEN Child.Childs = ''
        THEN(CAST(Child.ChildId AS VARCHAR(MAX)))
        ELSE(Child.Childs + '.' + CAST(Child.ChildId AS VARCHAR(MAX)))
    END AS VARCHAR(MAX))
        FROM UserType AS PrevGeneration
        INNER JOIN Hierarchy AS Child ON PrevGeneration.Id = Child.ParentId    
)
SELECT *
    FROM Hierarchy
OPTION(MAXRECURSION 32767)

在线查看 http://www.sqlfiddle.com/#!3/f50a6/8

递归限制！！！

在所有先前的查询中，我们使用了如下语法

OPTION(MAXRECURSION 32767)

这指定了 CTE 中的最大递归次数。 现在，如果我们不使用 OPTION(MAXRECURSION 32767)，则默认值为 100。

我们需要根据递归要求指定此数字。

如果需要更多递归！！！

MAXRECURSION 值等于 0 意味着递归级别没有限制，但请记住递归应在某个级别结束。

OPTION(MAXRECURSION 0)

要增加递归次数，不包括 CTE 的最大限制。 我们可以遵循一些链接中的说明，或者在 Google 上搜索一段时间。

限制

是的，可能有些我误解或表达了。 因此，如果您发现任何问题，请告诉我。

查找作为附件的 SQL 文件。

© . All rights reserved.