MySQL查询：为每个property匹配优先级缩略图或首行gallery图片

表结构信息 #

properties表 #

gallery表 #

查询需求 #

查询properties表的同时，为每个property匹配gallery表中的对应图片，匹配规则：

• 优先选择is_thumbnail = 1的图片；
• 若不存在符合上述条件的图片，则选择gallery表中匹配property_id的首行记录（按id升序的第一条）。

期望查询结果 #

完整SQL语句 #

SELECT 
    p.id AS id,
    p.property AS property,
    g.photo_name AS photo_name
FROM properties p
LEFT JOIN (
    SELECT 
        property_id,
        photo_name,
        -- 按规则排序：is_thumbnail=1排最前，然后按id升序取第一条
        ROW_NUMBER() OVER (
            PARTITION BY property_id 
            ORDER BY is_thumbnail DESC, id ASC
        ) AS rn
    FROM gallery
) g ON p.id = g.property_id AND g.rn = 1;

语句说明 #

1. 子查询中使用ROW_NUMBER()窗口函数，按property_id分组，先按is_thumbnail降序（让值为1的排在最前面），再按id升序排序，给每组的记录编号；
2. 主查询中只关联子查询里编号为1的记录，这样就保证每个property要么拿到缩略图，要么拿到对应gallery里的第一条记录；
3. 相比原语句的LEFT JOIN加WHERE条件的写法，这种方式能避免过滤掉没有匹配gallery记录的property（如果有的话），同时精准匹配到符合规则的图片。

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