如何无需多次关联同一张表获取带条件的两个不同参数？

针对你需要多次关联vocabulary字典表的场景，可以用以下几种方法避免重复JOIN，提升代码可读性和维护性：

方法1：条件聚合（全数据库兼容） #

核心思路是只关联一次vocabulary表，通过CASE语句或聚合函数，根据匹配的ID分别提取对应术语名称。

对应你的示例场景，SQL可改写为：

SELECT
    c.id AS id,
    MAX(CASE WHEN v.id = c.new_term THEN v.term_name END) AS new_term_name,
    MAX(CASE WHEN v.id = c.old_term THEN v.term_name END) AS old_term_name
FROM change c
LEFT JOIN vocabulary v ON v.id IN (c.new_term, c.old_term)
WHERE c.id = #{id}
GROUP BY c.id

如果需要关联5-7个术语字段，只需在IN条件中添加对应字段，再新增对应的CASE分支即可，无需重复JOIN。

方法2：JSON映射（适用于MySQL 8+/PostgreSQL等支持JSON的数据库） #

将vocabulary表的ID与术语名构建成JSON映射对象，直接通过ID取值，完全避免JOIN操作：

MySQL 8+版本 #

SELECT
    c.id AS id,
    JSON_EXTRACT(vocab_map, CONCAT('$.', c.new_term)) AS new_term_name,
    JSON_EXTRACT(vocab_map, CONCAT('$.', c.old_term)) AS old_term_name
FROM change c
CROSS JOIN (
    SELECT JSON_OBJECTAGG(id, term_name) AS vocab_map
    FROM vocabulary
) AS v
WHERE c.id = #{id}

PostgreSQL版本 #

SELECT
    c.id AS id,
    vocab_map ->> c.new_term::text AS new_term_name,
    vocab_map ->> c.old_term::text AS old_term_name
FROM change c
CROSS JOIN (
    SELECT jsonb_object_agg(id, term_name) AS vocab_map
    FROM vocabulary
) AS v
WHERE c.id = #{id}

该方案适合术语表数据不频繁更新的场景，若术语表更新频繁，可改用视图或物化视图优化性能。

方法3：LATERAL/APPLY关联（PostgreSQL/SQL Server专属） #

通过LATERAL（PostgreSQL）或APPLY（SQL Server）实现动态子查询，一次性获取所有需要的术语：

PostgreSQL（LATERAL JOIN） #

SELECT
    c.id AS id,
    t.new_term_name,
    t.old_term_name
FROM change c
LEFT JOIN LATERAL (
    SELECT
        MAX(CASE WHEN id = c.new_term THEN term_name END) AS new_term_name,
        MAX(CASE WHEN id = c.old_term THEN term_name END) AS old_term_name
    FROM vocabulary
    WHERE id IN (c.new_term, c.old_term)
) AS t ON true
WHERE c.id = #{id}

SQL Server（OUTER APPLY） #

SELECT
    c.id AS id,
    t.new_term_name,
    t.old_term_name
FROM change c
OUTER APPLY (
    SELECT
        MAX(CASE WHEN id = c.new_term THEN term_name END) AS new_term_name,
        MAX(CASE WHEN id = c.old_term THEN term_name END) AS old_term_name
    FROM vocabulary
    WHERE id IN (c.new_term, c.old_term)
) AS t
WHERE c.id = #{id}

性能说明 #

• 条件聚合方案兼容性最好，术语表数据量不大时性能优异；
• JSON映射方案适合术语表稳定的场景，一次构建映射后可多次复用，大幅减少JOIN开销；
• LATERAL/APPLY方案灵活性更强，适合需要动态筛选术语ID的场景。

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