V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
WaterWestBolus
V2EX  ›  MySQL

一题 MySQL 查询题求解

  •  
  •   WaterWestBolus · 2021-04-05 20:44:33 +08:00 · 3173 次点击
    这是一个创建于 1088 天前的主题,其中的信息可能已经有所发展或是发生改变。

    销售表 sales_dtl 有城市(city),销售经理(cbm),客户(customer),销售金额(sale_amt)三列字段,请用 sql 取出每个城市中销售金额为本城市前 10 名的销售经理的信息。

    是一题面试题,迫于 SQL 技术太差写不出答案。看了网上博客的资料,没看懂也没跑起来。故来求解

    20 条回复    2021-04-06 17:14:20 +08:00
    acr0ss
        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/),有**官方题解**。
    Oktfolio
        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
    Ptu2sha
        3
    Ptu2sha  
       2021-04-05 21:02:54 +08:00
    楼上果然不审题
    Oktfolio
        4
    Oktfolio  
       2021-04-05 21:03:54 +08:00
    噢,每个城市...
    Ptu2sha
        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
    大致这样写即可
    WaterWestBolus
        6
    WaterWestBolus  
    OP
       2021-04-05 21:10:00 +08:00
    @acr0ss thanks,看官方的有点累,我再研究一下。
    WaterWestBolus
        7
    WaterWestBolus  
    OP
       2021-04-05 21:12:21 +08:00
    @Ptu2sha 试了一下不太行。。我再看看。谢谢
    nuistzhou
        8
    nuistzhou  
       2021-04-05 21:12:23 +08:00 via iPhone
    window function 吧
    Ptu2sha
        9
    Ptu2sha  
       2021-04-05 21:13:54 +08:00
    @WaterWestBolus 报错了 substring_index 少写了 , 、你查下文档
    思路就是按城市分组 里面的销售额排序得到销售 然后取前十
    acr0ss
        10
    acr0ss  
       2021-04-05 21:18:34 +08:00
    @WaterWestBolus 官网题解区现成的语句,还有中文解释。
    shine20070522
        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;
    yeqizhang
        12
    yeqizhang  
       2021-04-05 22:43:01 +08:00 via Android
    不是四列吗……
    akira
        13
    akira  
       2021-04-06 01:59:20 +08:00
    只会逐个城市取 然后代码合并一起。。。
    xiaolanger
        14
    xiaolanger  
       2021-04-06 10:40:36 +08:00
    这个得用上变量了
    WaterWestBolus
        15
    WaterWestBolus  
    OP
       2021-04-06 11:01:11 +08:00
    @shine20070522 非常感谢,成了!
    raaaaaar
        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
    );
    raaaaaar
        17
    raaaaaar  
       2021-04-06 13:03:36 +08:00
    大致想了下,重点是这个:
    假设一共 n 个数,如果是从大往小排,那么排名第 m,就比这个数大的数的数目,就是 m-1,也就是说它前面又 m -1 个数,那么要找前 k 个数,只需要比这个数大的数目的个数小于 k 就行了,所以这里外面遍历确定一个数,然后里面找到比这个数大的数目小于 10 的就行了
    akiraX
        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
    xxlee
        19
    xxlee  
       2021-04-06 16:56:14 +08:00
    好奇 mysql 没有窗口函数 row_number over 么
    zc832097
        20
    zc832097  
       2021-04-06 17:14:20 +08:00
    @xxlee 8.0 才支持窗口函数 现在好多都还在用 5.x
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2995 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 14:42 · PVG 22:42 · LAX 07:42 · JFK 10:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.