SQL Server递归查询-按分层顺序查看

2vuwiymt 于 2022-10-22 发布在 SQL Server

关注(0)|答案(1)|浏览(172)

我需要从制造系统查询一些信息。有了这个论坛上的许多帖子，我能够构建一个递归查询，但现在我只能完成它了。
我有一个表格，显示了需要制造的组件以及所有子组件和组件。
简化如下：
|组件|组件|数量|
| ------------ | ------------ | ------------ |
|machine_a|front_cover|1|
|machine_a|side_cover|2|
|前盖|螺钉|4|
|front_cover|sheet_metal_a|1|
|side_cover|螺钉|4|
|side_cover|sheet_metal_b|1|
|machine_b|框|1|
|盒|螺钉|8|
|盒子|金属板|1|
现在我对machine_a及其所有组件/子组件感兴趣。这台机器由一次前盖和两次侧盖以及一些螺钉组成。
要列出特定机器的所有组件，我正在运行递归查询：

declare @assembly nchar(20) = 'machine_a';

WITH whole_machine AS (
    SELECT       
        assembly_parent, 
        component,
        amount,
        0 as Level,
        amount as total
   FROM       
        manufacturing
    WHERE assembly_parent=@assembly
    UNION ALL
    SELECT 
        man.assembly_parent, 
        man.component,
        man.amount,
        Level + 1,
        man.amount * mach.total as total
    FROM 
        manufacturing man
        INNER JOIN whole_machine mach
        ON mach.component = man.assembly_parent
)

select * from whole_machine;

这与预期一样，我得到了“machine_a”及其所有组件和正确级别：
|assembly_parent|component|amount|Level|total_amount|
| ------------ | ------------ | ------------ | ------------ | ------------ |
|machine_a|front_cover|1|0|1|
|machine_a|side_cover|2|0|2|
|side_cover|螺钉|4|1|8|
|side_cover|sheet_metal_b|1|1|2|
|front_cover|螺钉|4|1|4|
|front_cover|sheet_metal_a|1|1|1|
有没有办法让这个列表按层次排序？因此，与其先列出级别0、级别1、级别2等的内容，不如按照组件所属的顺序来显示它？有点像这样：

machine_a
    front_cover
        screw
        sheet_metal_a
    side_cover
        screw
        sheet_metal_b

这意味着它是上面的列表，但应该按以下顺序显示：
|assembly_parent|组件|数量|级别|总计|
| ------------ | ------------ | ------------ | ------------ | ------------ |
|machine_a|front_cover|1|0|1|
|front_cover|螺钉|4|1|4|
|front_cover|sheet_metal_a|1|1|1|
|machine_a|side_cover|2|0|2|
|side_cover|螺钉|4|1|8|
|side_cover|sheet_metal_b|1|1|2|

sql-server

来源：https://stackoverflow.com/questions/74151170/sql-server-recursive-query-view-in-hierarchical-order

1条答案

按热度按时间

vawmfj5a1#

你离我们很近。你需要一个所有部分的谱系。如果表中的每一行都有一个ID，则可以使用您已经使用的CTE构造这样的沿袭。

declare @assembly nchar(20) = 'machine_a';

with manufacturing as (
    select * from (values
        (1, 'machine_a', 'front_cover', 1),
        (2, 'machine_a', 'side_cover', 2),
        (3, 'front_cover', 'screw', 4),
        (4, 'front_cover', 'sheet_metal_a', 1),
        (5, 'side_cover', 'screw', 4),
        (6, 'side_cover', 'sheet_metal_b', 1),
        (7, 'machine_b', 'box', 1),
        (8, 'box', 'screw', 8),
        (9, 'box', 'sheet_metal', 1)
    ) as x(id, assembly_parent, component, amount)
)
, whole_machine AS (
    SELECT       
        assembly_parent, 
        component,
        amount,
        amount as total,

        0 as Level,
        h = cast( concat('/', id, '/') as varchar(max))
   FROM       
        manufacturing
    WHERE assembly_parent=@assembly
    UNION ALL
    SELECT 
        man.assembly_parent, 
        man.component,
        man.amount,
        man.amount * mach.total,

        Level + 1,
        h = cast( concat(mach.h, man.id, '/' ) as varchar(max))
    FROM 
        manufacturing man
        INNER JOIN whole_machine mach
        ON mach.component = man.assembly_parent
)

select *
from whole_machine
order by h;

注意，h可以转换为hierarchyid（我非常喜欢）。它的优点是能够与数据一起持久化到表中，因此不需要在查询时构造沿袭，而是可以在数据变异时完成。如果你有兴趣探索，我可以写下来。

赞(0）回复(0）举报 2022-10-22

我来回答

SQL Server递归查询-按分层顺序查看

1条答案

相关问题

热门标签

最新问答