如何利用SQL查询结果关联多表获取零件最新供应商信息

问题背景 #

需要生成包含图纸编号、零件ID、零件名称、最新供应商名称的表格，涉及三个数据表：

• BOM表：drawingN（图纸编号）、partsID（零件ID）、partsName（零件名称）
• purchase_history表：partsID、supplierID（供应商ID）、purchaseDate（采购日期）
• supplier_list表：supplierID、supplierName（供应商名称）

当前已实现两个独立查询：

1. 获取指定图纸下的零件清单：

SELECT drawingN, partsID, partsName
FROM BOM
WHERE drawingN = 'I16253'

返回结果：

2. 查询单个零件的最新有效供应商（排除代表取消/修改订单的supplierID 2991、2992）：

SELECT TOP 1 purchase_history.partsID, supplier_list.supplierName
FROM purchase_history
LEFT JOIN supplier_list ON purchase_history.supplierID = supplier_list.supplierID
WHERE partsID = 'A1234' AND supplierID <> '2991' AND supplierID <> '2992'
ORDER BY purchaseDate DESC

返回结果：

期望合并查询后得到统一结果：

解决方案 #

使用**窗口函数ROW_NUMBER()**可以高效实现需求，一次性获取指定图纸下所有零件的最新有效供应商信息：

SELECT 
    b.drawingN,
    b.partsID,
    b.partsName,
    sl.supplierName
FROM BOM b
LEFT JOIN (
    SELECT 
        ph.partsID,
        ph.supplierID,
        -- 按零件分组，采购日期倒序排序，最新记录排名为1
        ROW_NUMBER() OVER (PARTITION BY ph.partsID ORDER BY ph.purchaseDate DESC) AS rn
    FROM purchase_history ph
    WHERE ph.supplierID NOT IN ('2991', '2992') -- 排除无效供应商
) ph_ranked ON b.partsID = ph_ranked.partsID AND ph_ranked.rn = 1
LEFT JOIN supplier_list sl ON ph_ranked.supplierID = sl.supplierID
WHERE b.drawingN = 'I16253'
ORDER BY b.partsID;

逻辑说明 #

1. 子查询ph_ranked：对每个零件的有效采购记录（排除2991、2992），按采购日期倒序生成排名，最新的采购记录会被标记为rn=1。
2. 关联BOM表与子查询：只保留每个零件排名第一的采购记录，确保获取的是最新供应商。
3. 关联供应商表：通过supplierID匹配供应商名称，同时LEFT JOIN保证即使零件无有效采购记录，也能保留BOM中的基础信息。

如果使用支持TOP 1 WITH TIES的数据库（如SQL Server），也可以用以下简化写法：

SELECT 
    b.drawingN,
    b.partsID,
    b.partsName,
    sl.supplierName
FROM BOM b
LEFT JOIN (
    SELECT TOP 1 WITH TIES
        ph.partsID,
        ph.supplierID
    FROM purchase_history ph
    WHERE ph.supplierID NOT IN ('2991', '2992')
    ORDER BY ROW_NUMBER() OVER (PARTITION BY ph.partsID ORDER BY ph.purchaseDate DESC)
) ph_latest ON b.partsID = ph_latest.partsID
LEFT JOIN supplier_list sl ON ph_latest.supplierID = sl.supplierID
WHERE b.drawingN = 'I16253'
ORDER BY b.partsID;

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