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