MySQL JOIN查询排序后分组：获取商品缩略图及图片总数方案

原来的SQL语句没有显式筛选imgNum=1的缩略图记录，直接分组后未加入GROUP BY的非聚合字段会返回组内随机记录，因此无法保证拿到对应商品的缩略图信息。

方案1：双关联实现（兼容MySQL 5.x/8.x全版本） #

分别关联fileData表获取缩略图信息、统计单商品总图片数，写法如下：

SELECT 
    p.idx AS p_idx,
    f_thumb.imgNum,
    f_thumb.file_url,
    IFNULL(f_cnt.img_cnt, 0) AS img_cnt,
    p.reg_date
FROM products p
-- 关联取缩略图
LEFT JOIN fileData f_thumb 
    ON p.idx = f_thumb.p_idx 
    AND f_thumb.imgNum = 1
-- 关联统计单商品总图片数
LEFT JOIN (
    SELECT p_idx, COUNT(idx) AS img_cnt 
    FROM fileData 
    GROUP BY p_idx
) f_cnt ON p.idx = f_cnt.p_idx
-- 若只需返回有图片的商品，可加上下方筛选条件
-- WHERE f_thumb.idx IS NOT NULL
ORDER BY p.reg_date DESC;

方案说明 #

• 用LEFT JOIN保证即使商品没有设置缩略图、没有上传图片也能返回对应商品记录，不需要无图商品可改为INNER JOIN或者加上WHERE筛选条件
• 统计总图片数的子查询预先聚合每个商品的图片总量，避免分组逻辑和缩略图筛选逻辑冲突

方案2：窗口函数实现（仅支持MySQL 8.0+） #

如果使用的数据库支持窗口函数，也可以用分组排序的方式取缩略图+统计总数：

SELECT 
    p_idx,
    imgNum,
    file_url,
    img_cnt,
    reg_date
FROM (
    SELECT 
        p.idx AS p_idx,
        f.imgNum,
        f.file_url,
        COUNT(f.idx) OVER(PARTITION BY p.idx) AS img_cnt,
        p.reg_date,
        ROW_NUMBER() OVER(PARTITION BY p.idx ORDER BY CASE WHEN f.imgNum = 1 THEN 1 ELSE 2 END) AS rn
    FROM products p
    LEFT JOIN fileData f ON p.idx = f.p_idx
) t
WHERE rn = 1
ORDER BY reg_date DESC;

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