如何利用doctor_specialties表的ordinal字段生成主、次专科列？

嘿，这个需求用条件聚合来实现会比多次INNER JOIN高效得多，因为它只需要对doctor_specialties表扫描一次就能同时算出主、次专科，不用做多次关联。另外你之前写的SQL片段里WHERE子句的位置不对（正确顺序是FROM/JOIN在前，WHERE在后），下面给你具体的实现思路和SQL示例：

这种方法通过CASE WHEN结合聚合函数来一次性提取主、次专科，避免多次JOIN带来的性能损耗，是处理这类行转列需求的常用高效方案。

场景1：每个医生仅1个主专科+1个次专科（ordinal=2为次专科） #

如果业务规则里每个医生最多只有两个专科（ordinal=1为主，ordinal=2为次），可以直接用CASE WHEN匹配对应的ordinal值：

SELECT
    pd.name AS doctor_name,
    -- 提取ordinal=1的专科作为主专科
    MAX(CASE WHEN ds.ordinal = 1 THEN s.name END) AS primary_specialty,
    -- 提取ordinal=2的专科作为次专科
    MAX(CASE WHEN ds.ordinal = 2 THEN s.name END) AS secondary_specialty
FROM doctors pd
JOIN doctor_specialties ds ON pd.id = ds.doctor_id
JOIN specialties s ON ds.specialty_id = s.id
GROUP BY pd.id, pd.name;

场景2：每个医生有1个主专科+多个次专科（合并所有次专科） #

如果医生可能有多个次专科（ordinal>1），可以用字符串聚合函数把所有次专科合并成一个字段（不同数据库的聚合函数略有差异）：

MySQL 版本 #

SELECT
    pd.name AS doctor_name,
    MAX(CASE WHEN ds.ordinal = 1 THEN s.name END) AS primary_specialty,
    -- 用逗号分隔合并所有ordinal>1的专科
    GROUP_CONCAT(CASE WHEN ds.ordinal > 1 THEN s.name END SEPARATOR ', ') AS secondary_specialties
FROM doctors pd
JOIN doctor_specialties ds ON pd.id = ds.doctor_id
JOIN specialties s ON ds.specialty_id = s.id
GROUP BY pd.id, pd.name;

PostgreSQL 版本 #

SELECT
    pd.name AS doctor_name,
    MAX(CASE WHEN ds.ordinal = 1 THEN s.name END) AS primary_specialty,
    STRING_AGG(CASE WHEN ds.ordinal > 1 THEN s.name END, ', ') AS secondary_specialties
FROM doctors pd
JOIN doctor_specialties ds ON pd.id = ds.doctor_id
JOIN specialties s ON ds.specialty_id = s.id
GROUP BY pd.id, pd.name;

为什么这个方案比INNER JOIN高效？ #

• 条件聚合只需要对三张表做一次关联扫描，而多次INNER JOIN（比如分别JOIN主专科和次专科）会导致多次表关联与数据扫描，数据量越大性能差异越明显。
• 数据库引擎对聚合操作的优化通常优于多表关联，减少了中间结果集的生成与处理开销。

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