双层嵌套查询关联列实现：低库存产品关联供应商信息查询

需要从products表中查询库存数量≤3的产品，查询结果需包含所有产品数据，以及从BuyingReceipts表匹配的供应商建议，关联逻辑如下：

• 从products表筛选库存≤3的产品；
• 针对每个选中产品，在purchase表中查找该产品最后一次采购记录的r_index；
• 通过r_index获取对应的供应商名称；
• 将供应商名称关联到步骤1的产品列表中。

已写出单个产品对应供应商名称的查询语句：

select provider from BuyingReceipts as r
inner join
(select top(1) r_index from purchase where code = 9) p
on r.r_index = p.r_index

但无法写出完整的关联查询语句，请求技术帮助。

可以通过窗口函数或者关联子查询实现批量匹配每个产品的最新采购记录，以下是两种可行写法：

方法一：窗口函数实现（推荐，性能更优） #

SELECT 
    p.*,
    br.provider AS 供应商建议
FROM 
    products p
LEFT JOIN (
    -- 给每个产品的采购记录按时间倒序排序，标记最新的一条
    SELECT 
        code,
        r_index,
        ROW_NUMBER() OVER (PARTITION BY code ORDER BY purchase_time DESC) AS rn
    FROM 
        purchase
) pur ON p.code = pur.code AND pur.rn = 1
LEFT JOIN BuyingReceipts br ON pur.r_index = br.r_index
WHERE 
    p.stock <= 3;

说明： #

• 子查询用ROW_NUMBER()按产品code分组，采购时间purchase_time降序排序，给每组第一条（最新采购记录）标记rn=1；
• 用LEFT JOIN确保库存≤3的产品即使无采购记录也能被查出；
• 最后关联BuyingReceipts表获取供应商名称。

方法二：关联子查询实现 #

SELECT 
    p.*,
    (
        SELECT br.provider
        FROM purchase pur
        JOIN BuyingReceipts br ON pur.r_index = br.r_index
        WHERE pur.code = p.code
        ORDER BY pur.purchase_time DESC
        LIMIT 1 -- 注意数据库语法差异：SQL Server用TOP(1)，Oracle用FETCH FIRST 1 ROW ONLY
    ) AS 供应商建议
FROM 
    products p
WHERE 
    p.stock <= 3;

说明： #

• 针对每个库存≤3的产品，通过子查询直接定位其最新采购记录对应的供应商；
• 根据使用的数据库调整取第一条记录的语法。

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