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 · 31 天前 · 1875 次点击

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

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

    20 条回复    2021-04-06 17:14:20 +08:00
    acr0ss
        1
    acr0ss   31 天前   ❤️ 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   31 天前
    select cbm, sum(sale_amt) as amt
    from sales_dtl
    group by cbm
    order by amt desc
    limit 10
    Ptu2sha
        3
    Ptu2sha   31 天前
    楼上果然不审题
    Oktfolio
        4
    Oktfolio   31 天前
    噢,每个城市...
    Ptu2sha
        5
    Ptu2sha   31 天前
    substring_index(group_concat(cbm order by sale_amt),10) from sales_dtl group by city
    大致这样写即可
    WaterWestBolus
        6
    WaterWestBolus   31 天前
    @acr0ss thanks,看官方的有点累,我再研究一下。
    WaterWestBolus
        7
    WaterWestBolus   31 天前
    @Ptu2sha 试了一下不太行。。我再看看。谢谢
    nuistzhou
        8
    nuistzhou   31 天前 via iPhone
    window function 吧
    Ptu2sha
        9
    Ptu2sha   31 天前
    @WaterWestBolus 报错了 substring_index 少写了 , 、你查下文档
    思路就是按城市分组 里面的销售额排序得到销售 然后取前十
    acr0ss
        10
    acr0ss   31 天前
    @WaterWestBolus 官网题解区现成的语句,还有中文解释。
    shine20070522
        11
    shine20070522   31 天前   ❤️ 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   31 天前 via Android
    不是四列吗……
    akira
        13
    akira   31 天前
    只会逐个城市取 然后代码合并一起。。。
    xiaolanger
        14
    xiaolanger   30 天前
    这个得用上变量了
    WaterWestBolus
        15
    WaterWestBolus   30 天前
    @shine20070522 非常感谢,成了!
    raaaaaar
        16
    raaaaaar   30 天前
    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   30 天前
    大致想了下,重点是这个:
    假设一共 n 个数,如果是从大往小排,那么排名第 m,就比这个数大的数的数目,就是 m-1,也就是说它前面又 m -1 个数,那么要找前 k 个数,只需要比这个数大的数目的个数小于 k 就行了,所以这里外面遍历确定一个数,然后里面找到比这个数大的数目小于 10 的就行了
    akiraX
        18
    akiraX   30 天前
    组内自增序号辅助
    select * from (
    select a.*,
    case when @pre_city=a.city then @n:[email protected] + 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   30 天前
    好奇 mysql 没有窗口函数 row_number over 么
    zc832097
        20
    zc832097   30 天前
    @xxlee 8.0 才支持窗口函数 现在好多都还在用 5.x
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3722 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 02:08 · PVG 10:08 · LAX 19:08 · JFK 22:08
    ♥ Do have faith in what you're doing.