向大乌龟求一个SQL语句，关于BOM多层级展示的SQL语句

Question

涉及Bom表如下结构
#jzMatBom主表（ID ,Number,Name,MatID）
/说明 ID是主键系统自动增长整数类型，Number是Bom主表的编码，Name是Bom主表的名称，MatID是主表BOM对应的物料/
#jzMatBomDetail子表（ID,MatBomID,MatID,Many,SHL,JiliangdanweiID）
/说明 ID是主键系统自动增长整数类型，MatBomID是对应Bom主表的ID，MatID是对应引用的物料，Many是组成数量，SHL是损耗率/

#jzMat物料表（ID ,Number,Name,JiliangdanweiID）
/说明 ID是主键系统自动增长整数类型，Number是物料表的编码，Name是物料表的名称，JiliangdanweiID是物料的单位/

求一个SQL语句得到某个Bom层次清单

create table #jzMat (ID int,Number varchar(100),Name varchar(100),jiliangdanweiID int )
insert into #jzMat values(1348    ,    'xb00001'    ,    '箱包成品',    1)
insert into #jzMat values(1349    ,    'xb00002'    ,    '箱包上盖',    1    )
insert into #jzMat values(1350    ,    'xb00003'    ,    '箱包下盖',    1    )
insert into #jzMat values(1351    ,    'xb00004'    ,    '箱包上盖原料'    ,    1)
insert into #jzMat values(1352    ,    'xb00005'    ,    '箱包上盖原料'    ,    1)
insert into #jzMat values(1353    ,    'xb00006'    ,    '箱包下盖原料'    ,    1)
insert into #jzMat values(1354    ,    'xb00007'    ,    '箱包下盖原料'    ,    1)
insert into #jzMat values(1395    ,    'xb00008'    ,    '箱包布料'    ,    14)

select ID,',',Number,',',Name,',',MatID from jzMatBom where Name like '%箱包%'
create table #jzMatBom (ID int,Number varchar(100),Name varchar(100),MatID int )
insert into #jzMatBom values(146    ,    'xb00001'    ,    '箱包成品'    ,    1348)
insert into #jzMatBom values(147    ,    'xb00002'    ,    '箱包上盖'    ,    1349)
insert into #jzMatBom values(148    ,    'xb00003'    ,    '箱包下盖'    ,    1350)
insert into #jzMatBom values(162    ,    'xb00004'    ,    '箱包上盖原料'    ,    1351)


create table #jzMatBomDetail (ID int,MatBomID int ,MatID int,Many decimal,Sll decimal,JiliangdanweiID int)
insert into #jzMatBomDetail values(2293    ,    147    ,    1351    ,    1.00000000    ,    0.0000    ,    1)
insert into #jzMatBomDetail values(2294    ,    147    ,    1352    ,    2.00000000    ,    0.0000    ,    1)
insert into #jzMatBomDetail values(2295    ,    148    ,    1353    ,    2.00000000    ,    0.0000    ,    1)
insert into #jzMatBomDetail values(2296    ,    148    ,    1354    ,    4.00000000    ,    0.0000    ,    1)
insert into #jzMatBomDetail values(2299    ,    146    ,    1349    ,    1.00000000    ,    0.0000    ,    1)
insert into #jzMatBomDetail values(2300    ,    146    ,    1350    ,    1.00000000    ,    0.0000    ,    1)
insert into #jzMatBomDetail values(2444    ,    162    ,    1395    ,    0.50000000    ,    0.0000    ,    14)

求一个SQL得到如下结果：

可以用空格代替缩进
这是我用代码一行一行拼接的结果，sql语句结果就这样即可，层级是指BOM对应的第几层物料，1代表第一层，2代表第二层，3代表第三层。用空格多少来显示层次结构1个空格代表缩进去一点，2个代表缩进去更多！
层级 物料编码 物料名称 单位 用量
1 Xb00002 箱包上盖 1 1
2 （1个空格）xb00004 箱包上盖原料1 1 1
3 （2个空格）xb00008 箱包布料 14 0.5
2 （1个空格）xb00005 箱包上盖原料2 1 2
1 Xb00003 箱包下盖 1 1
2 （1个空格）xb00006 箱包下盖原料1 1 2
2 （1个空格）xb00007 箱包下盖原料2 1 4

Answer 1

让【宝码香车】来帮你解答，本回答参考DeepSeek编写，并整理提供。

如果还有疑问可以点击头像关注私信或评论。如果答案让您满意，请采纳、点赞、关注，非常感谢！

BOM多层级展示SQL解决方案

要实现BOM的多层级展示，我们需要使用递归公用表表达式(CTE)来遍历BOM的层级结构。以下是完整的SQL解决方案：

WITH BomHierarchy AS (
    -- 基础查询：获取顶层BOM项
    SELECT 
        b.ID,
        b.Number AS BomNumber,
        b.Name AS BomName,
        m.Number AS MatNumber,
        m.Name AS MatName,
        d.JiliangdanweiID AS UnitID,
        d.Many AS Quantity,
        1 AS Level,
        CAST(b.Number AS VARCHAR(1000)) AS Path
    FROM #jzMatBom b
    JOIN #jzMatBomDetail d ON b.ID = d.MatBomID
    JOIN #jzMat m ON d.MatID = m.ID
    WHERE b.Number = 'xb00001' -- 从箱包成品开始
    
    UNION ALL
    
    -- 递归查询：获取子级BOM项
    SELECT 
        b.ID,
        b.Number AS BomNumber,
        b.Name AS BomName,
        m.Number AS MatNumber,
        m.Name AS MatName,
        d.JiliangdanweiID AS UnitID,
        d.Many * bh.Quantity AS Quantity, -- 计算累计用量
        bh.Level + 1 AS Level,
        CAST(bh.Path + '->' + b.Number AS VARCHAR(1000)) AS Path
    FROM BomHierarchy bh
    JOIN #jzMatBom b ON bh.MatNumber = b.Number
    JOIN #jzMatBomDetail d ON b.ID = d.MatBomID
    JOIN #jzMat m ON d.MatID = m.ID
)


SELECT 
    Level AS 层级,
    REPLICATE(' ', (Level-1)*4) + MatNumber AS 物料编码,
    REPLICATE(' ', (Level-1)*4) + MatName AS 物料名称,
    UnitID AS 单位,
    Quantity AS 用量
FROM BomHierarchy
ORDER BY Path, Level;

说明

1. 这个SQL使用递归CTE来遍历BOM的层级结构：
   • 基础查询获取顶层BOM项（箱包成品）
   • 递归部分获取每个BOM项的子项

1. 关键点：
   • Level字段表示当前BOM项的层级
   • Path字段用于保持正确的排序顺序
   • REPLICATE(' ', (Level-1)*4)用于生成缩进空格（每级缩进4个空格）

1. 结果会按照BOM层级结构展示，每个子项比父项多4个空格的缩进。

1. 用量计算考虑了父项的用量，实现了用量的累计计算。

替代方案（使用字符串连接）

如果你需要更精确地控制缩进格式（如问题中要求的1个空格缩进），可以使用以下修改版本：

WITH BomHierarchy AS (
    -- 同上基础查询
    -- ...
)


SELECT 
    Level AS 层级,
    CASE 
        WHEN Level = 1 THEN MatNumber
        WHEN Level = 2 THEN ' ' + MatNumber
        WHEN Level = 3 THEN '  ' + MatNumber
        ELSE MatNumber
    END AS 物料编码,
    CASE 
        WHEN Level = 1 THEN MatName
        WHEN Level = 2 THEN ' ' + MatName
        WHEN Level = 3 THEN '  ' + MatName
        ELSE MatName
    END AS 物料名称,
    UnitID AS 单位,
    Quantity AS 用量
FROM BomHierarchy
ORDER BY Path, Level;

这个版本会严格按照问题要求，第一层不缩进，第二层缩进1个空格，第三层缩进2个空格。