获取关系的SQL查询

Two tables are defined below. Names are arranged in a parent-child relationship. how to Show a nested (tree) list of names including [Id], [Name] and [Level], where [Level] indicates the nest level from the top (Root: Level = 0; First children of Root: Level = 1; etc…).

下面定义了两个表。姓名以父子关系排列。如何显示嵌套(树)名称列表,包括[Id],[Name]和[Level],其中[Level]表示从顶部开始的嵌套级别(Root:Level = 0; Root的第一个子级:Level = 1等等......)

CREATE TABLE [Names] 
(
[Id] INT PRIMARY KEY,
[Name] VARCHAR(100)
)

CREATE TABLE [Relationships]
(
[Parent] [int] REFERENCES [Names]([Id]), 
[Child] [int] REFERENCES [Names]([Id])
)



INSERT [NAMES] VALUES (1,'FRANK')
INSERT [NAMES] VALUES (2,'JO')
INSERT [NAMES] VALUES (3,'MARY')
INSERT [NAMES] VALUES (4,'PETER')
INSERT [NAMES] VALUES (5,'MAY')

INSERT [RELATIONSHIPS] VALUES (1,0)
INSERT [RELATIONSHIPS] VALUES (2,1)
INSERT [RELATIONSHIPS] VALUES (3,2)
INSERT [RELATIONSHIPS] VALUES (4,1)
INSERT [RELATIONSHIPS] VALUES (5,2)

I am using ms sql server 2008

我正在使用ms sql server 2008

4 个解决方案

#1

I think this is sweet and simple way

我认为这是一种甜蜜而简单的方式

SELECT child AS ID  , N.Name , ISNULL(R.Parent, 0) AS Lavel   
FROM  Relationships R ,NAMES N 
WHERE  R.Child = N.Id  ORDER BY parent,child

ID  Name    Level
1   FRANK   0
2   JO      1
4   PETER   1
3   MARY    2
5   MAY     2

#2

common table expressions allow you to do recursive calls for things like this. Search for that. It will go something like this:

公用表表达式允许您对此类事件执行递归调用。搜索它。它会是这样的:

with cte as (
    select *, 1 as lvl
    from relationships as a
    join names as b ... 
    union
    select *, lvl + 1
    from ....
)
select * from cte;

I may not have it exactly right, but searching for cte recursion will help.

我可能没有完全正确,但搜索cte递归会有所帮助。

#3

Use Recursive CTE to do this.

使用递归CTE执行此操作。

;WITH cte
     AS (SELECT NAME,
                Parent,
                Child,
                0 AS level
         FROM   [Names] N
                JOIN Relationships r
                  ON r.Parent = n.Id
         WHERE  Child IS NULL
         UNION ALL
         SELECT b.NAME,
                b.Parent,
                b.Child,
                level + 1
         FROM   cte a
                JOIN (SELECT NAME,
                             Parent,
                             Child
                      FROM   [Names] N
                             JOIN Relationships r
                               ON r.Parent = n.Id) b
                  ON a.Parent = b.Child)
SELECT * FROM   cte

Note : In Relationship table first row (ie) INSERT [RELATIONSHIPS] VALUES (1,0) you cannot insert 0 in child column since Names table does not have such entry.

注意:在关系表的第一行(即)INSERT [RELATIONSHIPS] VALUES(1,0)中,您不能在子列中插入0,因为Names表没有这样的条目。

If it is the root then then you can use NULL instead of 0 like INSERT [RELATIONSHIPS] VALUES (1,null)

如果它是根然后你可以使用NULL而不是像INSERT [RELATIONSHIPS] VALUES(1,null)

#4

CREATE TABLE [Names] 
(
    [Id] INT PRIMARY KEY,
    [Name] VARCHAR(100)
)

CREATE TABLE [Relationships]
( 
    [Child] [int] REFERENCES [Names]([Id]),
    [Parent] [int] REFERENCES [Names]([Id])
)

INSERT [NAMES] VALUES (1,'FRANK')
INSERT [NAMES] VALUES (2,'JO')
INSERT [NAMES] VALUES (3,'MARY')
INSERT [NAMES] VALUES (4,'PETER')
INSERT [NAMES] VALUES (5,'MAY')

INSERT [RELATIONSHIPS] VALUES (1,Null)
INSERT [RELATIONSHIPS] VALUES (2,1)
INSERT [RELATIONSHIPS] VALUES (3,2)
INSERT [RELATIONSHIPS] VALUES (4,1)
INSERT [RELATIONSHIPS] VALUES (5,4)

 --first have a look at INSERT [RELATIONSHIPS] VALUES (1,0), you are 
    --saying 0 is the child of 1. I think you need to swap the column 
    --names? - this solution assumes you meant parent to be child 
    --and visa-versa - i.e. I swapped them above
 --second, do you need the relationships table? cant you just have a 
    --reports_to column in the names table?
 --third, you cant have a value of 0 in either column of your relationships 
    --table because of the FK constraint to names - no name exists 
    --with id = 0, insert NULL instead
--fourth, I changed May's manager so that it was clear that lvl & manager werent related

;with 
cte as 
    (select id, name, isnull(parent,0) as manager
    from Names n
        left join Relationships r
            on r.child = n.id),
r_cte as 
    (select 0 as lvl, id, name, manager
    from cte
    where manager = 0
    union all
    select r.lvl + 1, c.id, c.Name, c.manager
    from cte c
        inner join r_cte r
            on c.manager = r.id
    )
select * from r_cte

#1