针对你需要为SSRS报表开发查询，展示ERP中多层级结构物料对应的底层基础物料的需求，我整理了一个基于SQL递归CTE的解决方案，完全匹配你给出的表结构和业务逻辑。

需求回顾 #

我们需要逐层追溯物料的下级供应物料：

1. 从DMO_INVENTPRODMETHOD获取物料对应的生产方法ID
2. 用生产方法ID在DMO_INVENTPRODSUPPITEMS中找到下级供应物料
3. 重复步骤1-2，直到找到没有下级供应物料的生产方法ID
4. 最后从DMO_INVENTPRODBASEITEMS中获取该生产方法对应的基础物料

解决方案：递归CTE实现层级遍历 #

SQL Server的递归CTE（Common Table Expression）非常适合处理这种层级遍历的场景，它能自动逐层追踪物料关系，直到终止条件满足。

完整查询代码 #

WITH RecursiveBOM AS (
    -- 递归起点：指定初始查询的物料（这里以ITEMID='100'为例）
    SELECT 
        im.ITEMID AS CurrentItem,
        im.PRODMETHODID AS CurrentProdMethod,
        isi.SUPPLITEMID AS NextItem,
        1 AS Level -- 标记层级，方便查看追溯路径（可选）
    FROM DMO_INVENTPRODMETHOD im
    LEFT JOIN DMO_INVENTPRODSUPPITEMS isi ON im.PRODMETHODID = isi.PRODMETHODID
    WHERE im.ITEMID = '100'

    UNION ALL

    -- 递归部分：逐层查找下级物料的生产方法和供应物料
    SELECT 
        im.ITEMID AS CurrentItem,
        im.PRODMETHODID AS CurrentProdMethod,
        isi.SUPPLITEMID AS NextItem,
        rb.Level + 1 AS Level
    FROM RecursiveBOM rb
    JOIN DMO_INVENTPRODMETHOD im ON rb.NextItem = im.ITEMID
    LEFT JOIN DMO_INVENTPRODSUPPITEMS isi ON im.PRODMETHODID = isi.PRODMETHODID
    WHERE rb.NextItem IS NOT NULL -- 仅对还有下级物料的节点继续递归
)
-- 最终获取基础物料：筛选无下级物料的节点，关联基础物料表
SELECT 
    rb.CurrentItem AS EndItem,
    rb.CurrentProdMethod AS EndProdMethod,
    dib.ITEMID AS BaseItem
FROM RecursiveBOM rb
JOIN DMO_INVENTPRODBASEITEMS dib ON rb.CurrentProdMethod = dib.PRODMETHODID
WHERE rb.NextItem IS NULL;

代码逻辑解释 #

1. 递归起点：从指定的初始物料（示例中是100）出发，获取它的生产方法和对应的下级供应物料，同时标记层级为1。
2. 递归迭代：以上一轮得到的下级物料作为当前物料，重复查询它的生产方法和下级物料，层级递增，直到某个物料没有对应的下级供应物料（NextItem IS NULL）时停止递归。
3. 结果输出：从递归结果中筛选出没有下级物料的最终节点，关联DMO_INVENTPRODBASEITEMS表，得到对应的底层基础物料。

测试数据验证 #

先创建并插入你提供的测试数据：

-- 创建表结构（添加主键约束保证数据唯一性）
CREATE TABLE DMO_INVENTPRODMETHOD ( 
    ITEMID VARCHAR (10) NOT NULL, 
    PRODMETHODID VARCHAR (10) NOT NULL, 
    PRIMARY KEY (ITEMID)
);

CREATE TABLE DMO_INVENTPRODSUPPITEMS ( 
    SUPPLITEMID VARCHAR (10) NOT NULL, 
    PRODMETHODID VARCHAR (10) NOT NULL, 
    PRIMARY KEY (PRODMETHODID)
);

CREATE TABLE DMO_INVENTPRODBASEITEMS ( 
    ITEMID VARCHAR (10) NOT NULL, 
    PRODMETHODID VARCHAR (10) NOT NULL, 
    PRIMARY KEY (PRODMETHODID)
);

-- 插入测试数据
INSERT INTO [dbo].[DMO_INVENTPRODMETHOD] VALUES ('100', 'A123');
INSERT INTO [dbo].[DMO_INVENTPRODMETHOD] VALUES ('S200', 'E123');
INSERT INTO [dbo].[DMO_INVENTPRODMETHOD] VALUES ('S400', 'Z123');

INSERT INTO [dbo].[DMO_INVENTPRODSUPPITEMS] VALUES ('S200', 'A123');
INSERT INTO [dbo].[DMO_INVENTPRODSUPPITEMS] VALUES ('S400', 'E123');

INSERT INTO [dbo].[DMO_INVENTPRODBASEITEMS] VALUES ('BAAAB10', 'Z123');

执行递归查询后，会得到和需求示例完全匹配的结果：

适配SSRS报表 #

如果要在SSRS中动态查询任意物料，只需把初始物料的条件替换为报表参数即可，比如：

WHERE im.ITEMID = @ReportItemID

这样报表就能根据用户输入的物料ID，自动返回对应的底层基础物料。

内容的提问来源于stack exchange，提问作者Mike Dole