销售表 sales_dtl 有城市(city),销售经理(cbm),客户(customer),销售金额(sale_amt)三列字段,请用 sql 取出每个城市中销售金额为本城市前 10 名的销售经理的信息。
是一题面试题,迫于 SQL 技术太差写不出答案。看了网上博客的资料,没看懂也没跑起来。故来求解
1
acr0ss 2021-04-05 20:55:23 +08:00 2
典型的 top k 题型,答题思路大致是两种。
1. 连表查询(一般解法) 2. 窗口函数( MySQL version >= 8.0 ) 具体可以参考: [leetcode-cn ]( https://leetcode-cn.com/problems/department-top-three-salaries/comments/852822/),有**官方题解**。 |
2
Oktfolio 2021-04-05 21:00:22 +08:00
select cbm, sum(sale_amt) as amt
from sales_dtl group by cbm order by amt desc limit 10 |
3
Ptu2sha 2021-04-05 21:02:54 +08:00
楼上果然不审题
|
4
Oktfolio 2021-04-05 21:03:54 +08:00
噢,每个城市...
|
5
Ptu2sha 2021-04-05 21:07:22 +08:00
substring_index(group_concat(cbm order by sale_amt),10) from sales_dtl group by city
大致这样写即可 |
6
WaterWestBolus OP @acr0ss thanks,看官方的有点累,我再研究一下。
|
7
WaterWestBolus OP @Ptu2sha 试了一下不太行。。我再看看。谢谢
|
8
nuistzhou 2021-04-05 21:12:23 +08:00 via iPhone
window function 吧
|
9
Ptu2sha 2021-04-05 21:13:54 +08:00
@WaterWestBolus 报错了 substring_index 少写了 , 、你查下文档
思路就是按城市分组 里面的销售额排序得到销售 然后取前十 |
10
acr0ss 2021-04-05 21:18:34 +08:00
@WaterWestBolus 官网题解区现成的语句,还有中文解释。
|
11
shine20070522 2021-04-05 22:14:43 +08:00 1
select t.city, substring_index(group_concat(t.cbm order by sale_amt desc),',',10)
from ( select city,cbm, sum(sale_amt) sale_amt from sales_dtl group by 1,2 ) t group by 1; |
12
yeqizhang 2021-04-05 22:43:01 +08:00 via Android
不是四列吗……
|
13
akira 2021-04-06 01:59:20 +08:00
只会逐个城市取 然后代码合并一起。。。
|
14
xiaolanger 2021-04-06 10:40:36 +08:00
这个得用上变量了
|
15
WaterWestBolus OP @shine20070522 非常感谢,成了!
|
16
raaaaaar 2021-04-06 12:59:30 +08:00
select city, cbm, sale_amt
from sales_dtl as t1 where 10 > ( select count(*) from sales_dtl as t2 where t1.city = t2.city and t1.sale_amt < t2.sale_amt ); |
17
raaaaaar 2021-04-06 13:03:36 +08:00
大致想了下,重点是这个:
假设一共 n 个数,如果是从大往小排,那么排名第 m,就比这个数大的数的数目,就是 m-1,也就是说它前面又 m -1 个数,那么要找前 k 个数,只需要比这个数大的数目的个数小于 k 就行了,所以这里外面遍历确定一个数,然后里面找到比这个数大的数目小于 10 的就行了 |
18
akiraX 2021-04-06 16:51:26 +08:00
组内自增序号辅助
select * from ( select a.*, case when @pre_city=a.city then @n:=@n + 1 else @n:=1 end as i, # 判断城市是否切换,没切换累加序号,切换了从 1 开始 @pre_city := a.city # 记录当前城市到 pre_city,用于下一行判断,这一列必须在上面这列后面 from ( select city,cbm,sum(sale_amt) as total from sales_dtl group by city,cbm order by city,total desc # 分组求和,按城市、总金额倒序排序 ) a ) b where b.i <= 10 # 取 top n |
19
xxlee 2021-04-06 16:56:14 +08:00
好奇 mysql 没有窗口函数 row_number over 么
|