SQL Server中合并两个关联复杂查询以补充采购价格字段

核心逻辑说明 #

因为采购表需要为每个匹配的BOM物料取按采购日期倒序的第一条价格记录，不能直接和BOM结果做普通内连接，否则会因为一个物料对应多条采购记录导致BOM结果行数膨胀，因此需要通过“预先给采购记录按物料分组排序”的方式，只关联每个物料最新的一条采购数据。

可落地实现写法 #

写法1：窗口函数实现（推荐，大数据量下性能更优） #

该写法仅需扫描一次采购表，避免关联子查询逐行查询的性能损耗，完整SQL如下：

WITH cte(MainArt, secondaryArt, Quantity) AS (
    SELECT 
        t_mitm, 
        t_sitm, 
        t_qana
    FROM ttibom010201 
    WHERE TRIM(t_mitm) = 'ACL-4812070395-4'

    UNION ALL

    SELECT 
        ttibom010201.t_mitm, 
        ttibom010201.t_sitm, 
        ttibom010201.t_qana
    FROM ttibom010201 
    INNER JOIN cte ON cte.secondaryArt = ttibom010201.t_mitm
),
-- 提前为每个物料的采购记录按日期排序，标记最新记录序号
purchase_sorted AS (
    SELECT 
        t_item,
        t_pric,
        t_orno,
        ROW_NUMBER() OVER (PARTITION BY t_item ORDER BY t_ddte DESC) AS record_rank
    FROM ttdpur401201
)
SELECT 
    cte.*,
    dsc.t_cuni, 
    dsc.t_dsca,
    ps.t_pric AS latest_purchase_price
FROM cte
LEFT JOIN ttcibd001201 dsc 
    ON dsc.t_item = cte.secondaryArt 
-- 仅关联每个物料排名第一的最新采购记录
LEFT JOIN purchase_sorted ps
    ON ps.t_item = cte.secondaryArt
    AND ps.record_rank = 1
WHERE TRIM(cte.secondaryArt) LIKE N'30%'

写法2：关联子查询实现（改造成本最低） #

该写法完全沿用原单条采购查询的逻辑，仅在select列中通过关联子查询取对应物料的最新价格，适配原有代码逻辑的改造成本最低，完整SQL如下：

WITH cte(MainArt, secondaryArt, Quantity) AS (
    SELECT 
        t_mitm, 
        t_sitm, 
        t_qana
    FROM ttibom010201 
    WHERE TRIM(t_mitm) = 'ACL-4812070395-4'

    UNION ALL

    SELECT 
        ttibom010201.t_mitm, 
        ttibom010201.t_sitm, 
        ttibom010201.t_qana
    FROM ttibom010201 
    INNER JOIN cte ON cte.secondaryArt = ttibom010201.t_mitm
)
SELECT 
    cte.*,
    dsc.t_cuni, 
    dsc.t_dsca,
    -- 逐行匹配当前secondaryArt对应的最新采购价
    (
        SELECT TOP 1 t_pric 
        FROM ttdpur401201 pur
        WHERE pur.t_item = cte.secondaryArt
        ORDER BY pur.t_ddte DESC
    ) AS latest_purchase_price
FROM cte
LEFT JOIN ttcibd001201 dsc 
    ON dsc.t_item = cte.secondaryArt 
WHERE TRIM(cte.secondaryArt) LIKE N'30%'

注意事项 #

• 原独立采购查询使用的是t_item like '%30102024%'的模糊匹配逻辑，合并到BOM查询时必须改为精确匹配secondaryArt的完整物料值，否则会匹配到其他无关物料的采购价格，导致数据错误
• 两种写法均采用左关联逻辑，若某个BOM物料在采购表中没有对应记录，价格字段会返回NULL，不会过滤BOM本身的查询结果
• 当采购表数据量较大时，优先选用窗口函数的写法，可有效降低查询耗时

内容的提问来源于stack exchange，提问作者Borislav Stefanov