SQL 混合排序

本文的写作思路是来自群友分享在群里的一道练习题，笔者觉得这道题有点小难度，且这类需求也比较常见。具体的需求描述请看下图。

咋一看，有点摸不着头脑。既要按照 score 排序，又要考虑到 A、A-1、A-2 这种数据之间的关系并使用另一种排序规则。

据分享这道题的朋友说，实际上这是一份店铺的销量数据。出题人出于某方面的考虑把表的字段名称改了，name 是店铺名称，名称中带有“-”表示分店，score 是销售额。出题人希望能依据城市、销售额查看各个店铺的销售数据，并且当存在分店时，分店能紧挨在总店后面按照 id 排序。

如果按照规则 ORDER BY city,score DESC 排序，我们可以做到让每个总店先按销售额排序，但没法保证分店能挨着总店排在一起。

    id  city    NAME     score  
------  ------  ------  --------
     1  a       A            100
     9  a       F             95
     4  a       A-2           90
     2  a       A-1           80
     5  b       D             85
     6  b       B             75
     3  b       C             70
    10  b       G             65
     8  b       B-1           50
     7  b       E             30

如果能够把分店和总店标记为一组（比如从 A、A-1、A-2 提取出共同的 ‘A’ 作为同组的标识 base_name），按照规则 ORDER BY city,base_name,id 排序，就能把总店和分店排在一起，却不能做到依据销售额的从高到低展示各总店的数据。

    id  city    NAME     score  
------  ------  ------  --------
     1  a       A            100
     2  a       A-1           80
     4  a       A-2           90
     9  a       F             95
     6  b       B             75
     8  b       B-1           50
     3  b       C             70
     5  b       D             85
     7  b       E             30
    10  b       G             65

不过，我们只需变通一下，把总店的销售额当成总店和分店的另一个同组标识（和 base_name 不一样的地方在于，这个新标识可拿来做排序），假设这个字段叫做 base_score，那么 A、A-1、A-2 对应的 base_score 都是 100，B 和 B-1 的 base_score 是 75 。

按照规则 ORDER BY city,base_score,id 排序就能满足题目的要求。

在 MySQL 中，使用函数 SUBSTRING_INDEX() 可以提取出我们想要的数据。比如，从 ‘A-1’ 中分别提取出 ‘A’ 和 ‘1’ 。

SELECT 
  SUBSTRING_INDEX('A-1', '-', 1) AS base,
  SUBSTRING_INDEX('A-1', '-', -1) AS seq;
  

base    seq     
------  --------
A       1   

分店要获取到总店的销售额，只需一次自关联就能做到，完整的 SQL 实现：

WITH t1 AS 
(SELECT 
  *,
  IF(
    INSTR(NAME, '-'),
    SUBSTRING_INDEX(NAME, '-', 1),
    NAME
  ) AS base_name
FROM
  t) 
SELECT 
  t1.id,
  t1.city,
  t1.name,
  t1.score 
FROM
  t1 
  LEFT JOIN t 
    ON t.name = t1.base_name 
ORDER BY t1.city,
  t.score DESC,
  t1.id 

其中，t 是存储原数据的表名。

最终输出的结果 >>>

    id  city    name     score  
------  ------  ------  --------
     1  a       A            100
     2  a       A-1           80
     4  a       A-2           90
     9  a       F             95
     5  b       D             85
     6  b       B             75
     8  b       B-1           50
     3  b       C             70
    10  b       G             65
     7  b       E             30