求助：编写MySQL内连接查询实现商品照片横向列展示（单商品最多3张）

嘿，这个把纵向数据转成横向的需求很常见，我给你准备了两种解决方案，分别适配不同版本的MySQL，完全贴合你的要求：

方案一：MySQL 8.0+（支持窗口函数） #

这个方案用ROW_NUMBER()窗口函数给每个商品的照片编号，再通过条件聚合转成列，代码简洁易懂：

SELECT 
    a.item_name,
    MAX(CASE WHEN rn = 1 THEN b.photo_file END) AS photo_file_1,
    MAX(CASE WHEN rn = 2 THEN b.photo_file END) AS photo_file_2,
    MAX(CASE WHEN rn = 3 THEN b.photo_file END) AS photo_file_3
FROM ITEMS a
-- 如果你只想保留有照片的商品，把LEFT JOIN改成INNER JOIN即可
LEFT JOIN (
    -- 给每个商品的照片按id顺序编号，只保留前3张
    SELECT 
        item_id, 
        photo_file,
        ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) AS rn
    FROM PHOTOS
) b ON a.id = b.item_id
WHERE rn <= 3 OR rn IS NULL -- 保留无照片的商品
GROUP BY a.id, a.item_name
ORDER BY a.item_name;

代码解释： #

• 子查询里的ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id)：按商品分组，给每个商品的照片按id从小到大编号（rn为1、2、3...）
• 主查询用MAX(CASE WHEN...)：把每个编号对应的照片放到对应的列中，MAX的作用是聚合时保留非NULL值，自动忽略NULL
• LEFT JOIN保证即使商品没有照片也会显示（所有photo列都是NULL），如果只需要有照片的商品，换成INNER JOIN就行

方案二：MySQL 5.x（不支持窗口函数） #

如果你的MySQL版本低于8.0，用用户变量来手动实现照片编号：

SELECT 
    a.item_name,
    MAX(CASE WHEN rn = 1 THEN b.photo_file END) AS photo_file_1,
    MAX(CASE WHEN rn = 2 THEN b.photo_file END) AS photo_file_2,
    MAX(CASE WHEN rn = 3 THEN b.photo_file END) AS photo_file_3
FROM ITEMS a
LEFT JOIN (
    SELECT 
        item_id, 
        photo_file,
        -- 用变量给每个商品的照片编号
        @rn := IF(@prev_item = item_id, @rn + 1, 1) AS rn,
        @prev_item := item_id
    FROM PHOTOS, (SELECT @rn := 0, @prev_item := 0) AS vars
    ORDER BY item_id, id
    HAVING rn <= 3
) b ON a.id = b.item_id
GROUP BY a.id, a.item_name
ORDER BY a.item_name;

代码解释： #

• @rn和@prev_item是用户变量，用来记录当前编号和上一个商品id
• 当处理同一个商品的照片时，编号递增；换商品时重置为1
• HAVING rn <=3直接过滤掉每个商品的第4张及以后的照片

运行结果 #

不管用哪个方案，都会得到你想要的横向结果：

（注：如果用INNER JOIN，laptop这一行会被过滤掉，和你原来的内连接结果一致）

内容的提问来源于stack exchange，提问作者Lukáš Ježek