MySQL：按第二列排序（非空时），同时保持第一列的顺序

MySQL：按第二列排序（非空时），同时保持第一列的顺序

本文探讨了在 MySQL 中，如何根据 title 列进行排序，同时将具有相同非空 series_id 的电影组合在一起。针对 series_id 为 NULL 的情况，仅按 title 排序；而对于非 NULL 的 series_id，则按 series_order 排序，并基于 series 表的 title 进行分组。本文提供了一种使用嵌套子查询实现的解决方案，以满足这种复杂的排序需求。

在实际的数据库应用中，我们经常会遇到复杂的排序需求。例如，我们需要先按照一个字段排序，然后在该字段相同的情况下，再按照另一个字段排序。更复杂的情况是，第二个排序字段可能存在 NULL 值，我们需要针对 NULL 值和非 NULL 值采取不同的排序策略。

本文将以一个具体的例子，讲解如何在 MySQL 中实现这种复杂的排序需求。假设我们有两个表：media 和 series。media 表包含电影的信息，包括 title（电影标题）、series_id（系列 ID）和 series_order（系列中的顺序）。series 表包含系列的信息，包括 id（系列 ID）和 title（系列标题）。

我们的目标是：

1. 首先按照 title 列进行排序。
2. 对于具有相同非空 series_id 的电影，将它们组合在一起，并按照 series_order 进行排序。
3. 对于 series_id 为 NULL 的电影，仅按照 title 进行排序。
4. 如果 series_id 不为空，则按照系列中最小的 series_order 对应的 title 进行排序。

为了实现这个目标，我们可以使用嵌套子查询。以下是 SQL 查询语句：

SELECT m2.title grouping_title, m1.title, COALESCE(m2.title, m1.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;

代码解释：

1. 内部子查询：

   SELECT series_id, title FROM media m3 WHERE series_order = (SELECT MIN(series_order) FROM media WHERE series_id = m3.series_id)

   这个子查询的目的是找到每个 series_id 对应的最小 series_order 的 title。这个 title 将作为该系列的基础标题，用于排序。

2. LEFT JOIN：

   LEFT JOIN ( ... 上面的子查询 ... ) m2 ON m1.series_id = m2.series_id

   我们将 media 表（别名 m1）与上面的子查询（别名 m2）进行左连接。这样，对于每个电影，如果它属于一个系列，我们就可以获取到该系列的基础标题。

3. COALESCE 函数：

   COALESCE(m2.title, m1.title)

   COALESCE 函数用于返回第一个非 NULL 的参数。如果 m2.title（系列的基础标题）不为 NULL，则返回它；否则，返回 m1.title（电影的标题）。这样，我们就得到了用于排序的标题。

4. ORDER BY：

   ORDER BY COALESCE(m2.title, m1.title), m1.series_order

   最后，我们按照 COALESCE 函数的结果和 series_order 进行排序。

注意事项：

• 这个解决方案使用了嵌套子查询，可能会影响查询性能。在数据量很大的情况下，可以考虑使用其他优化方法，例如创建索引或使用临时表。
• 如果 series_order 列存在 NULL 值，则 NULL 值会被排在最前面。如果需要将 NULL 值排在最后面，可以使用 ORDER BY COALESCE(m2.title, m1.title), m1.series_order IS NULL, m1.series_order。
• 在某些情况下，修改表结构，增加一个额外的列来存储排序所需的“基础标题”可能是一个更优的方案，可以避免复杂的 SQL 查询。

总结：

本文介绍了一种使用嵌套子查询在 MySQL 中实现复杂排序需求的方法。通过使用 LEFT JOIN 和 COALESCE 函数，我们可以灵活地控制排序的逻辑，满足各种复杂的业务需求。在实际应用中，我们需要根据具体情况选择合适的解决方案，并注意优化查询性能。