T-SQL层次结构查询

Question

T-SQL层次结构查询

sql-serverloopst-sqlhierarchycommon-table-expression

5

我有一个包含层级关系数据的表格：

这是一个样例数据，包括id、parent id、名称、代码（有时未填）、层级和isroot列。在实际情况下，将会有多个层级，但现在让我们看一个简化的例子。

我需要循环遍历所有记录，并找到在层次结构的任何层级中ID为空的行：

- ID范围在6到10之间的行应该被返回，因为它们没有填写代码。 - 从1到5行不应该被返回，因为代码在层级中的某个地方提到了。

如何使用T-SQL解决此问题？

我想到的唯一解决方案是递归(cte或WHILE)，但我尝试实现的过于复杂，无法解决问题。

- Ingvar

我认为一个递归的CTE会很好，其中你可以使用一个CASE语句来检查当前迭代的“code”列并设置标志。 - JNevill

3个回答

4

以下是另一个例子，供那些仍然在处理层级数据方面有困难的人参考（就像我一样）。假设我们有以下层级结构：

CEO
|-- Sales Director  
│   |-- Sales Manager 1
│   `-- Sales Manager 2 
`-- Technical Director
    |-- Product Manager
    |-- R&D Team Lead
    `-- QA Team Lead

使用递归CTE获取每个节点的级别:

with cte as (
    select id, parentId, roleName, 1 as lvl from roles where id = 1 -- root node
    union all
    select r.id, r.parentId, r.roleName, cte.lvl+1 as lvl from roles r -- child nodes
    inner join cte on cte.id = r.parentid
)
select * from cte;

获取每个节点的路径：

with cte as (
    select id, roleName,     cast(roleName as varchar(200)) as hierPath 
    from roles where id = 1 
    union all
    select r.id, r.rolename, cast(cte.hierPath + ' / ' + r.rolename as varchar(200)) as hierPath
    from roles r 
    inner join cte on cte.id = r.parentid
)
select * from cte;

使用row_number()和power()来获取排序好的层级树结果（先显示父节点，然后是其所有子节点，再是每个子节点的所有子节点，以此类推）：

with cte as (
    select id, roleName, cast(roleName as varchar(200)) as hierPath, 1 as lvl, 
    row_number()over(partition by parentid order by roleName) / power(10.0,1) as sortNo
    from roles where id = 1 
    union all
    select r.id, r.rolename, cast(cte.hierPath + ' / ' + r.rolename as varchar(200)) as hierPath, cte.lvl+1 as lvl,
    cte.sortNo + row_number()over(partition by r.parentid order by r.roleName) / power(10.0,cte.lvl+1) as sortNo
    from roles r 
    inner join cte on cte.id = r.parentid
)
select * from cte 
order by sortNo;

设置测试数据：

create table roles (
    id int not null,
    parentId int,
    roleName varchar(50) not null
);

insert into roles 
    (id, parentId, roleName)
values 
    (1, null, 'CEO'),
    (2, 1, 'Sales Director'),
    (3, 1, 'Technical Director'),
    (4, 2, 'Sales Manager 1'),
    (5, 2, 'Sales Manager 2'),
    (6, 3, 'Product Manager'),
    (7, 3, 'R&D Team Lead'),
    (8, 3, 'QA Team Lead');

- Rohim Chou

0

应该可以使用这样的查询：

; with cte as 
(
select id, parent_id,code,parent_id as RootId from tableT where IsRootLevel=1
UNION ALL
select T2.id,T2.parent_id,T2.code,T1.RootId as RootId from tableT T2 join
cte T1 on T1.id=T2.parent_id and IsRootLevel=0
)   
,
cte2 as
(select id,MAX(case when code ='' then NULL else code end) over( partition by RootId) as code from cte)

select T1.* from tableT T1 left join cte2 T2 
on T1.id=T2.id
where T2.code is NULL

查看演示

- DhruvJoshi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JNevill · Accepted Answer

略微不同于@DhruvJoshi的答案，因为它可能会更有用：

WITH recCTE AS
(
    SELECT 
        id, 
        parent_id, 
        CASE WHEN CODE IS NOT NULL THEN 1 ELSE 0 END as code_check,
        1 as depth,
        CAST(id as VARCHAR(50)) as path
    FROM table
    WHERE isRootLevel = 1
    UNION ALL
    SELECT
        table.id,
        table.parent_id,
        CASE WHEN CODE IS NOT NULL OR reccte.code_check = 1 THEN 1 ELSE 0 END,
        depth + 1 as depth,
        reccte.path + CAST(table.id AS varchar(10)) as path
    FROM
        recCTE 
        INNER JOIN table ON
            recCTE.ID = table.parent_id
    WHERE depth < 20 /*just in case you start cycling/endless looping*/
)
SELECT * FROM recCTE where code_check = 0 ORDER BY path, depth;