一题 MySQL 查询题求解

典型的 top k 题型，答题思路大致是两种。
1. 连表查询（一般解法）
2. 窗口函数（ MySQL version >= 8.0 ）

具体可以参考： [leetcode-cn ]( https://leetcode-cn.com/problems/department-top-three-salaries/comments/852822/)，有**官方题解**。

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

Ptu2sha

2021-04-05 21:02:54 +08:00

楼上果然不审题

Oktfolio

2021-04-05 21:03:54 +08:00

噢，每个城市...

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
大致这样写即可

WaterWestBolus

2021-04-05 21:10:00 +08:00

@acr0ss thanks，看官方的有点累，我再研究一下。

WaterWestBolus

2021-04-05 21:12:21 +08:00

@Ptu2sha 试了一下不太行。。我再看看。谢谢

nuistzhou

2021-04-05 21:12:23 +08:00 via iPhone

window function 吧

Ptu2sha

2021-04-05 21:13:54 +08:00

@WaterWestBolus 报错了 substring_index 少写了，、你查下文档
思路就是按城市分组里面的销售额排序得到销售然后取前十

acr0ss

2021-04-05 21:18:34 +08:00

@WaterWestBolus 官网题解区现成的语句，还有中文解释。

shine20070522

2021-04-05 22:14:43 +08:00

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;

yeqizhang

2021-04-05 22:43:01 +08:00 via Android

不是四列吗……

akira

2021-04-06 01:59:20 +08:00

只会逐个城市取然后代码合并一起。。。

xiaolanger

2021-04-06 10:40:36 +08:00

这个得用上变量了

WaterWestBolus

2021-04-06 11:01:11 +08:00

@shine20070522 非常感谢，成了！

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
);

raaaaaar

2021-04-06 13:03:36 +08:00

大致想了下，重点是这个：
假设一共 n 个数，如果是从大往小排，那么排名第 m，就比这个数大的数的数目，就是 m-1，也就是说它前面又 m -1 个数，那么要找前 k 个数，只需要比这个数大的数目的个数小于 k 就行了，所以这里外面遍历确定一个数，然后里面找到比这个数大的数目小于 10 的就行了

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

xxlee

2021-04-06 16:56:14 +08:00

好奇 mysql 没有窗口函数 row_number over 么

zc832097

2021-04-06 17:14:20 +08:00

@xxlee 8.0 才支持窗口函数现在好多都还在用 5.x