MySQL：基于可能无匹配值的关联表实现机构排序

这个需求我之前做类似SaaS系统的时候碰到过，核心要点是必须保证所有机构都被查询到，同时还要结合订阅表的排名来排序——毕竟有些机构可能还没买任何订阅，这部分绝对不能漏掉。

核心思路 #

首先得用LEFT JOIN关联机构表和订阅表，因为INNER JOIN只会保留两边都有匹配的记录，直接就把没订阅的机构过滤掉了，不符合需求。然后通过条件排序来区分“有订阅”和“无订阅”的机构顺序。

另外要注意：一个机构可能对应多个订阅（不同地点各一个），所以还要考虑排序时用哪个排名——比如取该机构的最高优先级排名（最小的ranking值，因为1>2>…>5），或者保留所有订阅条目展示。

方案1：展示所有机构的所有订阅条目（含无订阅的机构） #

如果需要把机构的每个订阅都列出来，同时确保无订阅的机构也显示，SQL可以这么写：

SELECT 
    a.id AS agency_id,
    a.name AS agency_name,
    s.location_id,
    s.ranking
FROM agency a
LEFT JOIN subscription s 
    ON a.id = s.agency_id
ORDER BY 
    -- 有订阅的按ranking升序（1在前），无订阅的排最后
    CASE 
        WHEN s.ranking IS NOT NULL THEN s.ranking 
        ELSE 6  -- 因为排名是1-5，用6把无订阅的放后面
    END ASC,
    -- 同排名的机构按名称排序
    a.name ASC;

方案2：每个机构只显示一条，取最优排名 #

如果希望每个机构只展示一条记录，用它的最高优先级排名（最小的ranking）来排序，就需要用聚合函数配合分组：

SELECT 
    a.id AS agency_id,
    a.name AS agency_name,
    MIN(s.ranking) AS best_ranking  -- 取最小的排名代表最优优先级
FROM agency a
LEFT JOIN subscription s 
    ON a.id = s.agency_id
GROUP BY a.id, a.name  -- 按机构分组，确保每个机构一条记录
ORDER BY 
    CASE 
        WHEN best_ranking IS NOT NULL THEN best_ranking 
        ELSE 6 
    END ASC,
    a.name ASC;

调整无订阅机构的排序位置 #

如果想把没有订阅的机构放在最前面，只需要把ELSE后面的值改成0就行：

CASE 
    WHEN best_ranking IS NOT NULL THEN best_ranking 
    ELSE 0  -- 0比1小，无订阅的会排在最前面
END ASC

注意事项 #

• 不同数据库的语法细节可能略有差异（比如PostgreSQL里NULLS LAST可以直接处理空值排序，不过用CASE的写法兼容性更好）；
• 如果订阅表的agency_id和location_id有唯一约束（每个地点最多一个订阅），那分组的时候不会有重复问题，放心用就行。

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