MySQL三表关联查询：按条件选列并过滤用户头像数据

需求与表结构 #

• users表：获取与查询参数uid匹配的用户的username
• matches表：获取匹配记录的id，并根据查询参数动态生成uid：若查询参数uid等于matches.liker，则取matches.liked，否则取matches.liker
• pic_urls表：仅获取每个用户profile_pic = 1的唯一头像记录

原SQL存在的问题 #

1. 别名语法错误：AS matches.uid不符合SQL语法，别名不能带表名前缀
2. 关联条件错误：用不存在的matches.uid字段关联users表，实际应该用动态生成的uid值
3. 过滤位置可优化：profile_pic = 1放在WHERE子句会先关联所有数据再过滤，放在JOIN条件更高效

修正后的SQL #

SELECT
    matches.id,
    IF(matches.liker = ${uid}, matches.liked, matches.liker) AS uid,
    users.username,
    pic_urls.url AS profpic
FROM matches
JOIN users
    ON users.id = IF(matches.liker = ${uid}, matches.liked, matches.liker)
JOIN pic_urls
    ON pic_urls.user_id = users.id
    AND pic_urls.profile_pic = 1

关键说明 #

• 动态生成的uid直接作为关联users表的条件，确保关联到正确的用户数据
• 将profile_pic = 1放在pic_urls的JOIN ON条件中，在关联阶段就过滤掉不符合条件的头像数据，减少后续处理的数据量
• 由于profile_pic = 1是唯一的，关联后不会出现重复行，保证结果集的正确性

内容的提问来源于stack exchange，提问作者Bobby Wan-Kenobi