SQL Server 使用递归CTE生成按深度排序的层次结构结果，而不使用heiarchyid

iyfjxgzm 于 2022-12-17 发布在其他

关注(0)|答案(2)|浏览(141)

我想先查询按深度排序的层次结构结果，而不使用SQL的heiarchyid内置函数。本质上，我希望在不使用任何花哨函数的情况下完成深度排序。
我提供了一个包含以下记录的临时表：
| 身份证|产品标识|订单1|名称1|
| - ------|- ------|- ------|- ------|
| 1个|零|1个|约什|
| 第二章|零|第二章|玛丽|
| 三个|零|三个|乔治|
| 四个|1个|1个|乔|
| 五个|1个|第二章|杰夫|
| 六个|第二章|1个|边缘|
| 七|第二章|第二章|摩尔|
| 八个|第二章|三个|最大值|
| 九|三个|1个|加仑|
| 十个|三个|第二章|牵索|
| 十一|四个|1个|托德|
| 十二|四个|第二章|阿瓦|
| 十三|九|1个|罗恩|
| 十四|九|第二章|票据|
| 十五|九|一百|帕特|
其中p_Id是父记录的id，order 1实际上就是显示深度优先输出的顺序。为了说明为什么我的查询不能完全工作，我将最后一条记录的order 1设置为100，而不是3。但是，这最终并不重要，因为100和3都在前一个order 1值之后。2.正确结果表的示例如下所示：
| 身份证|产品标识|订单1|名称1|后代|
| - ------|- ------|- ------|- ------|- ------|
| 1个|零|1个|约什|约什|
| 四个|1个|1个|乔|乔希/乔|
| 十一|四个|1个|托德|乔希/乔/托德|
| 十二|四个|第二章|阿瓦|乔希/乔/阿瓦|
| 五个|1个|第二章|杰夫|乔希/杰夫|
| 第二章|零|第二章|玛丽|玛丽|
| 六个|第二章|1个|边缘|玛丽/玛格|
| 七|第二章|第二章|摩尔|玛丽/摩尔|
| 八个|第二章|三个|最大值|玛丽/最大值|
| 三个|零|三个|乔治|乔治|
| 九|三个|1个|加仑|乔治/加仑|
| 十三|九|1个|罗恩|乔治/加仑/罗恩|
| 十五|九|第二章|票据|乔治/加尔/比尔|
| 十四|九|一百|帕特|乔治/加仑/帕特|
| 十个|三个|第二章|牵索|乔治/盖伊|
我的结果示例如下所示：
| 身份证|产品标识|订单1|名称1|后代|水平|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 1个|零|1个|约什|约什|第一款|
| 四个|1个|1个|乔|乔希/乔|一、一|
| 十一|四个|1个|托德|乔希/乔/托德|一、一、一、一|
| 十二|四个|第二章|阿瓦|乔希/乔/阿瓦|一、一、二|
| 五个|1个|第二章|杰夫|乔希/杰夫|一、二|
| 第二章|零|第二章|玛丽|玛丽|.2分|
| 六个|第二章|1个|边缘|玛丽/玛格|二、一|
| 七|第二章|第二章|摩尔|玛丽/摩尔|二、二|
| 八个|第二章|三个|最大值|玛丽/最大值|二、三|
| 三个|零|三个|乔治|乔治|三分|
| 九|三个|1个|加仑|乔治/加仑|三、一|
| 十三|九|1个|罗恩|乔治/加仑/罗恩|三、一、一|
| 十五|九|一百|帕特|乔治/加仑/帕特|三、一、一百|
| 十四|九|第二章|票据|乔治/加尔/比尔|三、一、二|
| 十个|三个|第二章|牵索|乔治/盖伊|三、二|
在这里我创建了一个levels列，它实际上连接了order 1值，并用句点分隔它们。这几乎返回了正确的结果，但由于我是按此字符串排序的（数字和句号），.3.1.100的水平值将出现在.3.1.2之前，这并不是想要的输出应该是什么样子。我确信有一个不同的方法来返回正确的深度顺序。见下面的代码生成一个临时表，以及我用来生成错误输出的代码（目前为止）。

if object_id('tempdb..#t1') is not null drop table #t1
CREATE TABLE #t1 (Id int, p_Id int, order1 int, name1 varchar(150))

INSERT into #t1 VALUES 
   (1, null, 1, 'josh'),
   (2, null, 2, 'mary'),
   (3, null, 3, 'george'),
   (4, 1, 1, 'joe'),
   (5, 1, 2, 'jeff'),
   (6, 2, 1, 'marg'),
   (7, 2, 2, 'moore'),
   (8, 2, 3, 'max'),
   (9, 3, 1, 'gal'),
   (10, 3, 2, 'guy'),
   (11, 4, 1, 'tod'),
   (12, 4, 2, 'ava'),
   (13, 9, 1, 'ron'),
   (14, 9, 2, 'bill'),
   (100, 9, 100, 'pat');

select * from #t1

-- Looking to generate heiarchy results ordered by depth --
; with structure as (

    -- Non-recursive term.
    -- Select the records where p_Id is null

    select  p.Id,
            p.p_Id,
            p.order1,
            p.name1,
            cast(p.name1 as varchar(64)) as Descendants,
            cast(concat('.', p.order1) as varchar(150)) as levels 
        from #t1 p 
        where p.p_Id is null

        union all

        -- Recursive term.
        -- Treat the records from previous iteration as parents.
        -- Stop when none of the current records have any further sub records.

        select  c.Id,
                c.p_Id,
                c.order1,
                c.name1,
                cast(concat(p.Descendants, '/', c.name1) as varchar(64)) as Descendants,
                cast(concat(p.levels, '.', c.order1) as varchar(150)) as levels
            from #t1 c                               -- c being the 'child' records
                inner join structure p                   -- p being the 'parent' records
                    on c.p_Id = p.Id

)

select  *
    from structure
    order by replace(levels, '.', '') asc

sql-server

来源：https://stackoverflow.com/questions/74830430/using-recursive-cte-to-generate-hierarchy-results-ordered-by-depth-without-the-u

2条答案

按热度按时间

34gzjxbg1#

就拿第二个例子来说，正如OP所指出的，我最初的答案对10个以上的孩子是不成立的。那么，我们能做什么呢（OP的建议）是用零填充值到一个恒定的长度。但是什么长度呢？我们需要取一个节点下的最大数量的子节点，并将其添加到最大值或order中，因此对于提供的示例，这是100 + 3。然后取其长度（3），并在每个order中填充0到3位数。这意味着我们将始终按需排序。

declare @PadLength int = 0;

select @PadLength = max(children)
from (
    select len(convert(varchar(12),max(order1)+count(*))) children
    from #t1
    group by p_Id
) x;

-- Looking to generate heiarchy results ordered by depth --
with structure as (
    -- Non-recursive term
    -- Select the records where p_Id is null
    select
        p.Id [Id]
        , p.p_Id [ParentId]
        , p.order1 [OrderBy]
        , p.name1 [Name]
        , cast(p.name1 as varchar(64)) Descendants
        , concat('.', right(replicate('0',@Padlength) + convert(varchar(12),p.order1), @PadLength)) Levels
    from #t1 p 
    where p.p_Id is null

    union all

    -- Recursive term
    -- Treat the records from previous iteration as parents.
    -- Stop when none of the current records have any further sub records.
    select
        c.Id,
        c.p_Id,
        c.order1,
        c.name1,
        cast(concat(p.Descendants, '/', c.name1) as varchar(64)),
        concat(p.levels, '.', right(replicate('0',@Padlength) + convert(varchar(12),c.order1), @PadLength))
    from #t1 c -- c being the 'child' records
    inner join structure p on c.p_Id = p.Id -- p being the 'parent' records
)
select *
from structure
order by replace(levels, '.', '') asc;

赞(0）回复(0）举报 2022-12-17

wwwo4jvm2#

注意：如果某个节点下的子节点超过10个，这个答案就不成立了。
所以你遇到的问题是你是按字符串而不是数字排序的，所以字符串100在字符串2之前，但是你需要按字符串排序来处理层次结构，因此，一种解决方案是基于order1列将order1替换为row_number()，同时它仍然是一个数字，并使用row_number()构建您的排序字符串。
因此，您可以替换：

cast(concat(p.levels, '.', c.order1) as varchar(150)) as levels

与

cast(concat(p.levels, '.', row_number() over (order by c.Order1)) as varchar(150))

给出完整的查询

with structure as (

    -- Non-recursive term.
    -- Select the records where p_Id is null

    select  p.Id,
            p.p_Id,
            p.order1,
            p.name1,
            cast(p.name1 as varchar(64)) as Descendants,
            cast(concat('.', p.order1) as varchar(150)) as levels 
        from #t1 p 
        where p.p_Id is null

        union all

        -- Recursive term.
        -- Treat the records from previous iteration as parents.
        -- Stop when none of the current records have any further sub records.

        select  c.Id,
                c.p_Id,
                c.order1,
                c.name1,
                cast(concat(p.Descendants, '/', c.name1) as varchar(64)) as Descendants,
                cast(concat(p.levels, '.', row_number() over (order by c.Order1)) as varchar(150))
            from #t1 c                               -- c being the 'child' records
                inner join structure p                   -- p being the 'parent' records
                    on c.p_Id = p.Id

)
select  *
    from structure
    order by replace(levels, '.', '') asc;

返回所需的结果。
注意：好问题，写得好。

赞(0）回复(0）举报 2022-12-17

我来回答

SQL Server 使用递归CTE生成按深度排序的层次结构结果，而不使用heiarchyid

2条答案

相关问题

热门标签

最新问答