MySQL多字段排序：按系列分组后按标题排序

本文介绍如何在MySQL中实现一种复杂的排序需求：首先将具有相同非空 series_id 的记录分组在一起，并在组内按 series_order 排序；对于 series_id 为空的记录，则直接按 title 排序。文章提供了一种基于自连接和 COALESCE 函数的解决方案，避免了修改表结构，并保证了在不同环境下的排序一致性。

在实际应用中，我们经常会遇到需要根据多个字段进行排序，并且各个字段的优先级不同的情况。本文将介绍一种在MySQL中实现复杂排序的方法，即优先按照系列（series_id）分组，然后在组内按照系列顺序（series_order）排序，最后再按照标题（title）排序。

问题描述

假设我们有一个 media 表，包含以下字段：

• title: 媒体标题
• series_id: 系列ID，可以为空
• series_order: 系列顺序

我们的目标是：

1. 如果 series_id 为空，则直接按照 title 排序。
2. 如果 series_id 不为空，则将具有相同 series_id 的记录分组在一起，并在组内按照 series_order 排序。
3. 最终，整个结果集按照系列分组后的“系列标题”进行排序，系列标题取该系列中 series_order 最小的记录的 title。

解决方案

为了实现上述排序需求，我们可以使用自连接和 COALESCE 函数。基本思路是：

1. 通过自连接 media 表，找到每个 series_id 对应的最小 series_order 的记录，将其 title 作为该系列的“系列标题”。
2. 使用 COALESCE 函数，如果 series_id 为空，则使用 title 作为排序依据；否则，使用“系列标题”作为排序依据。
3. 最后，按照 series_order 进行排序。

SQL 代码

SELECT m2.title grouping_title, m1.title, COALESCE(m2.title, m1.title) AS sort_title, m1.series_order
FROM media m1
LEFT JOIN (
    SELECT series_id, title
    FROM media m3
    WHERE series_order = (SELECT MIN(series_order) FROM media WHERE series_id = m3.series_id)
) m2 ON m1.series_id = m2.series_id
ORDER BY COALESCE(m2.title, m1.title), m1.series_order;

代码解释

• m1: media 表的别名，用于表示主查询。
• m2: media 表的别名，用于表示子查询，用于查找每个 series_id 对应的最小 series_order 的记录。
• COALESCE(m2.title, m1.title): 如果 m2.title 不为空，则返回 m2.title，否则返回 m1.title。这样，我们就实现了按照系列分组后的“系列标题”进行排序。
• ORDER BY COALESCE(m2.title, m1.title), m1.series_order: 首先按照 COALESCE(m2.title, m1.title) 排序，然后在组内按照 series_order 排序。
• grouping_title: 展示系列分组标题
• sort_title: 展示最终排序依据

示例

假设 media 表包含以下数据：

执行上述 SQL 代码后，得到的结果如下：

注意事项

• 该解决方案依赖于 series_order 字段的正确性，即同一个 series_id 对应的 series_order 必须是唯一的。
• 如果 series 表存在，并且包含系列标题信息，则可以使用 LEFT JOIN 连接 media 表和 series 表，从而获取系列标题。
• 如果数据量非常大，可以考虑创建索引来优化查询性能。例如，可以创建 series_id 和 series_order 的联合索引。

总结

本文介绍了一种在MySQL中实现复杂排序的方法，即优先按照系列分组，然后在组内按照系列顺序排序，最后再按照标题排序。该解决方案基于自连接和 COALESCE 函数，可以灵活地应对各种复杂的排序需求。在实际应用中，可以根据具体情况进行调整和优化，以达到最佳的性能和效果。