本文的写作思路是来自群友分享在群里的一道练习题,笔者觉得这道题有点小难度,且这类需求也比较常见。具体的需求描述请看下图。
咋一看,有点摸不着头脑。既要按照 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