MySQL查询需求：获取人员关联主图，无主图则取首张

嘿，我来帮你把原来的子查询改成JOIN写法，这里提供两种适配不同场景的方案，都能满足你的需求：优先取isPrimary=1的主图，无主图则取该人员的首张图片，同时保证所有人员信息都能被查询到。

方案一：使用窗口函数（推荐，适配多数现代数据库） #

这种方法利用窗口函数给每个人员的图片排序，优先主图，再按图片ID升序取首张，然后关联人员表：

SELECT p.*, img.id AS primaryImageId
FROM Persons p
LEFT JOIN (
    SELECT 
        personId,
        id,
        -- 按人员分组，主图排第一，无主图则按图片ID升序取第一张
        ROW_NUMBER() OVER (
            PARTITION BY personId 
            ORDER BY isPrimary DESC, id ASC
        ) AS img_rank
    FROM Images
) img ON p.id = img.personId AND img.img_rank = 1
ORDER BY p.id DESC;

逻辑说明： #

1. 子查询里用ROW_NUMBER()窗口函数，按personId分组，排序规则是：
   • 先按isPrimary降序：isPrimary=1的图片会排在同组的最前面
   • 再按id升序：没有主图时，取ID最小的那张（也就是你说的首张）
2. 只保留每组里img_rank=1的图片，再和Persons表做左连接，确保没有图片的人员也能出现在结果里。

方案二：双重左连接（适配不支持窗口函数的老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.7及以前），可以用两次左连接分别关联主图和首张图片，再用COALESCE优先取主图：

SELECT p.*, COALESCE(primary_img.id, first_img.id) AS primaryImageId
FROM Persons p
-- 关联主图
LEFT JOIN Images primary_img 
    ON p.id = primary_img.personId AND primary_img.isPrimary = 1
-- 关联每个人员的首张图片（用MIN(id)取最早的那张）
LEFT JOIN (
    SELECT personId, MIN(id) AS id
    FROM Images
    GROUP BY personId
) first_img ON p.id = first_img.personId
ORDER BY p.id DESC;

逻辑说明： #

1. 第一个左连接直接匹配isPrimary=1的主图（符合你说的“最多存在一张主图”的条件）
2. 第二个左连接通过分组取每个人员的最小图片ID，得到首张图片
3. 用COALESCE函数优先返回主图ID，主图不存在时返回首张图片ID，完美匹配你的需求。

这两种方案都能替代原来的子查询，而且在大数据量场景下，合理利用索引的话性能会更优哦～

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