数据库根据两列数据得到层级关系SQL Queries to Manage Hierarchical or Parent-child Relational Rows in SQL Server

SQL Queries to Manage Hierarchical or Parent-child Relational Rows in SQL Server

Introduction

Here, in this post, we will try to manage data with hierarchical relation or parent-child relation of a specific table in SQL server. Our special concentration would be over.

• Show Generations of each row
• Find all possible parents of a specific row
• Find all possible childs of a specific row
• Show all possible parents at a column with a separator
• Show all possible child’s at a column with a separator

Background

Let’s pretend:

• a row can have only one parent or Null as no parent
• there is at least a row, without parent (parentId is null)
• and at least a row, without any child

Here is our table schema:

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

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

Let’s populate the table with data:

/*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

So our populated table would be like:

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

Check in live http://www.sqlfiddle.com/#!3/f50a6/1

Now how to find all these generations, parents or childs using SQL for a specific row …!!!

The answer is using recursion. But to use this recursion, we need something called CTE (Common Table Expressions) or in syntax “WITH” in SQL. If we don’t have any idea about it, we can start with the links or Google for few moments.

• http://msdn.microsoft.com/en-us/library/ms175972.aspx
• http://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx
• http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER

So let’s start with pretty basic stuff.

 

Regular Join

Joining table with itself based on condition, where ones parentId is equal to another’s 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;

 Check in live http://www.sqlfiddle.com/#!3/f50a6/2

SELECT a.*,
       b.Name
FROM UserType AS a
    LEFT JOIN dbo.UserType AS b
        ON a.ParentId = b.Id;

Row Generation

The procedure is something like:

• All rows with no parent (NULL), assign generation 0 to them
• Find rows where parent belongs to the generation 0, and assign increased generation to itself
• Do until the recursion is finished

/*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)

 Check in live http://www.sqlfiddle.com/#!3/f50a6/3

 

WITH Hierarchy (PositionID, MemberID, UniqueID, Generation, ManagerID)
AS (SELECT FirtGeneration.PositionID,
           FirtGeneration.MemberID,
           FirtGeneration.UniqueID,
           0,
           FirtGeneration.ManagerID
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy AS FirtGeneration
    WHERE (
              FirtGeneration.ManagerID IS NULL
              OR FirtGeneration.ManagerID = '0'
          )
          AND FirtGeneration.ClientID = 9185516
    UNION ALL
    SELECT NextGeneration.PositionID,
           NextGeneration.MemberID,
           NextGeneration.UniqueID,
           Parent.Generation + 1,
           Parent.PositionID
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy AS NextGeneration
        INNER JOIN Hierarchy AS Parent
            ON NextGeneration.ManagerID = Parent.PositionID
    WHERE NextGeneration.ClientID = 9185516)
SELECT *
FROM Hierarchy
ORDER BY Hierarchy.PositionID;

 

只有unique id和manager id两列数据

WITH Hierarchy (UniqueID, Generation, ManagerID)
AS (SELECT FirtGeneration.UniqueID,
           0,
           FirtGeneration.ManagerID
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy AS FirtGeneration
    WHERE (
              FirtGeneration.ManagerID IS NULL
              OR FirtGeneration.ManagerID = '0'
          )
    UNION ALL
    SELECT NextGeneration.UniqueID,
           Parent.Generation + 1,
           Parent.UniqueID
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy AS NextGeneration
        INNER JOIN Hierarchy AS Parent
            ON NextGeneration.ManagerID = Parent.UniqueID)
SELECT *
FROM Hierarchy
WHERE UniqueID IN ( 'E8189', 'E8192' )
ORDER BY Hierarchy.UniqueID;

 

All Possible Parents

Check http://stackoverflow.com/a/21233413/2948523

Here, we are trying to find all possible parents of a row where its Id = 5.

• Starts with selecting the row where Id = 5
• Find other rows where its id is equal to previously selected ParentId
• And continue reduction

--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)

 Check in live http://www.sqlfiddle.com/#!3/f50a6/5

 

有一个问题是，如果2个人的上级，是同一个的话。那么这个parent的记录，会被筛选出来2次。

WITH tblParent
AS (SELECT *
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy
    WHERE UniqueID IN ( 'E8189' )
    UNION ALL
    SELECT tblChild.*
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy AS tblChild
        INNER JOIN tblParent
            ON tblChild.UniqueID = tblParent.ManagerID)
SELECT *
FROM tblParent;

 

All Possible Parents in a Column

Here, we are showing all the possible parent Ids at the column with a specific separator ‘.’

/*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);

 

 Check in live http://www.sqlfiddle.com/#!3/f50a6/7

 

筛选所有的parent的路径，

 

WITH tblParent
AS (SELECT *,
           0 AS Generation
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy
    WHERE UniqueID IN ( 'E8189' )
    UNION ALL
    SELECT tblChild.*,
           tblParent.Generation + 1
    FROM dbo.tbm_cti_CustomTableItem_EmployeeHierarchy AS tblChild
        INNER JOIN tblParent
            ON tblChild.UniqueID = tblParent.ManagerID)
SELECT *
FROM tblParent;

 

这里是先筛选目标数据，设置generation为0，然后找parent，逐代往上

DECLARE @id BIGINT;
SET @id = 5;
WITH tblParent
AS (SELECT *,
           0 AS Generation
    FROM UserType
    WHERE Id = @id
    UNION ALL
    SELECT UserType.*,
           tblParent.Generation + 1
    FROM UserType
        JOIN tblParent
            ON UserType.Id = tblParent.ParentId)
SELECT *
FROM tblParent
--WHERE Id <> @id
OPTION (MAXRECURSION 32767);

但是这里的generation和之前的例子里面是相反的

 

作者:Chuck Lu    GitHub