MySQL单查询替代循环多次SELECT实现指定VID关联数据查询

需求回顾 #

需要查询指定VID的记录，规则如下：

• 若原记录的AID为NULL，直接保留该记录
• 若原记录的AID非NULL，替换为同一表中TID与该AID匹配的关联记录
  最终结果需包含所有符合条件的记录，替代当前循环多次执行SELECT的实现方式。

表结构：tbl 包含字段 ID, VID, AID, TID 及其他业务字段（如title等）

解决方案 #

使用UNION ALL拆分两种场景并合并结果，单条SQL语句即可实现需求：

-- 保留AID为NULL的原指定VID记录
SELECT t1.*
FROM tbl t1
WHERE t1.vid = 24 AND t1.aid IS NULL

UNION ALL

-- 匹配AID非NULL的原记录对应的TID关联记录
SELECT t2.*
FROM tbl t1
INNER JOIN tbl t2 ON t1.aid = t2.tid
WHERE t1.vid = 24 AND t1.aid IS NOT NULL;

逻辑说明 #

1. 第一部分查询直接筛选VID=24且AID为空的记录，完全对应伪代码中aid != null为false时的处理逻辑
2. 第二部分通过INNER JOIN关联自身表，找到VID=24且AID非空的记录对应的TID匹配记录，对应伪代码中aid != null为true时的查询逻辑
3. UNION ALL用于合并两部分结果，此处两部分结果无重叠场景，无需使用去重的UNION

补充说明 #

如果需要兼容AID非空但无对应TID匹配记录的场景（即保留原记录而非丢弃），可将INNER JOIN改为LEFT JOIN，并使用COALESCE优先取关联记录，无关联时保留原记录：

SELECT COALESCE(t2.id, t1.id) AS id,
       COALESCE(t2.vid, t1.vid) AS vid,
       COALESCE(t2.aid, t1.aid) AS aid,
       COALESCE(t2.title, t1.title) AS title,
       COALESCE(t2.tid, t1.tid) AS tid
       -- 其他业务字段按上述格式补充
FROM tbl t1
LEFT JOIN tbl t2 ON t1.aid = t2.tid
WHERE t1.vid = 24;

此写法会保留所有VID=24的记录，当有匹配的关联记录时用关联记录替换，无匹配时保留原记录。

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