如何在MySQL中实现关联查询与分组以生成指定结果

要实现将每个商品的批次横向展开为lot1到lot4列，并关联库存信息的需求，可以使用行转列结合窗口函数的方式实现，具体SQL如下：

SELECT 
    s.id,
    s.name,
    MAX(CASE WHEN t.rn = 1 THEN t.lot ELSE 0 END) AS lot1,
    MAX(CASE WHEN t.rn = 2 THEN t.lot ELSE 0 END) AS lot2,
    MAX(CASE WHEN t.rn = 3 THEN t.lot ELSE 0 END) AS lot3,
    MAX(CASE WHEN t.rn = 4 THEN t.lot ELSE 0 END) AS lot4,
    s.stock
FROM tblstock s
LEFT JOIN (
    -- 给每个商品的批次分配序号
    SELECT 
        name,
        lot,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY lot) AS rn
    FROM tblinputs
) t ON s.name = t.name
GROUP BY s.id, s.name, s.stock
ORDER BY s.id;

关键逻辑说明 #

1. 子查询生成批次序号：使用ROW_NUMBER() OVER (PARTITION BY name ORDER BY lot)给每个商品（name）下的批次按升序分配行号rn，这样每个商品的批次会依次得到1、2、3、4的序号。
2. 行转列处理：通过MAX(CASE...)聚合函数，将每个行号对应的批次值映射到lot1到lot4列，没有对应批次的位置填充0。
3. 关联库存表：使用LEFT JOIN关联tblstock和子查询，确保每个库存记录都能被查询到，最后按id分组排序，匹配目标结果格式。

原语句的问题 #

• 表名错误：引用了不存在的tblitems，应该是tblstock
• 关联条件错误：tblitems.id=tblinputs.name逻辑错误，正确关联条件是tblstock.name=tblinputs.name
• 缺少行转列逻辑：直接按lot分组无法将纵向的批次数据转为横向列结构

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