如何按优先级高效获取单个关联file.id？优化SQL多表查询

数据表结构 #

files:     id, name;
character: id, type;
person:    id, characterId FK character.id;
monster:   id, characterId FK character.id;
vampire:   id, characterId FK character.id;
person_files:  personId FK person.id,   fileId FK file.id, order;
monster_files: monsterId FK monster.id, fileId FK file.id, order;
vampire_files: vampireId FK vampire.id, fileId FK file.id, order;

查询需求 #

给定character.id，需按以下优先级返回目标file.id：

• 优先从person_files中选取order最小的记录；
• 若person_files无匹配，则从vampire_files或monster_files中选取order最小的记录；
• 无任何匹配时返回null。

原有多次LEFT JOIN方案会重复关联files表，需优化为仅关联一次files的高效查询。

优化后的SQL查询 #

SELECT f.id AS target_file_id
FROM files f
JOIN (
    -- 合并所有关联记录并标记优先级
    SELECT 
        pf.fileId, 
        pf.order, 
        1 AS priority
    FROM person_files pf
    JOIN person p ON pf.personId = p.id
    WHERE p.characterId = :input_character_id
    
    UNION ALL
    
    SELECT 
        vf.fileId, 
        vf.order, 
        2 AS priority
    FROM vampire_files vf
    JOIN vampire v ON vf.vampireId = v.id
    WHERE v.characterId = :input_character_id
    
    UNION ALL
    
    SELECT 
        mf.fileId, 
        mf.order, 
        2 AS priority
    FROM monster_files mf
    JOIN monster m ON mf.monsterId = m.id
    WHERE m.characterId = :input_character_id
    
    -- 按优先级和order升序取第一条
    ORDER BY priority, `order`
    LIMIT 1
) AS ranked_files ON f.id = ranked_files.fileId

-- 无匹配时返回null
UNION ALL
SELECT NULL
LIMIT 1;

优化说明 #

1. 统一数据收集：用UNION ALL合并三个关联表的有效记录，同时为person_files标记最高优先级（1），vampire_files和monster_files标记次优先级（2），避免重复关联files表。
2. 精准筛选最优结果：子查询内按priority和order升序排序，LIMIT 1直接获取符合优先级的最小order记录。
3. 兼容无匹配场景：通过UNION ALL拼接SELECT NULL，确保无任何匹配时返回null。

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