如何按特定顺序获取MySQL数据？自定义排序需求求解

需求分析 #

你需要先按role ASC, name ASC确定每个街道（street）组的排序优先级，将同街道的记录聚合在一起，组内再按role ASC, name ASC排序。

高效实现方案 #

方法一：关联分组聚合子查询（兼容MySQL 5.7及以上）

通过子查询预先计算每个街道的最小role及对应最小name，以此作为组的排序依据，再关联原表完成排序，逻辑清晰且执行效率高。

SELECT d.*
FROM data d
JOIN (
    -- 计算每个街道的最小role，以及该role下的最小name作为组排序键
    SELECT 
        street,
        MIN(role) AS min_role,
        MIN(CASE WHEN role = MIN(role) THEN name END) AS min_name
    FROM data
    GROUP BY street
) s ON d.street = s.street
-- 先按组排序键排序，再按组内的role、name排序
ORDER BY s.min_role, s.min_name, d.role, d.name;

方法二：窗口函数（适合MySQL 8.0及以上）

利用窗口函数直接为每条记录计算所属街道的组排序键，无需额外关联操作，代码更简洁。

SELECT name, role, street
FROM (
    SELECT 
        *,
        -- 计算当前街道的最小role
        MIN(role) OVER (PARTITION BY street) AS min_role,
        -- 计算当前街道最小role对应的最小name
        MIN(CASE WHEN role = MIN(role) OVER (PARTITION BY street) THEN name END) OVER (PARTITION BY street) AS min_name
    FROM data
) t
ORDER BY min_role, min_name, role, name;

结果验证 #

执行上述任一SQL，将得到你预期的结果：

效率优化建议 #

• 两种方案均优于你之前的嵌套查询，子查询/窗口函数仅需一次计算即可完成分组排序逻辑；
• 建议为street、role、name建立联合索引：INDEX idx_street_role_name (street, role, name)，可大幅提升聚合和排序的执行效率。

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