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
sytnishizuiai
V2EX  ›  MySQL

MySQL 求助, group by 城市行业后获取工资最低的一条数据

  •  
  •   sytnishizuiai · 2019-09-11 13:51:36 +08:00 · 5116 次点击
    这是一个创建于 1898 天前的主题,其中的信息可能已经有所发展或是发生改变。

    例如字段有 id,city,business,money 4 个字段,我想要获取各个 city 下不同 business 下的最低 money 并得到 id,

    我试了好几种方法,每次获取的值都是 id 靠前的,即便使用 MIN ( money ),id 也是无法对应。

    34 条回复    2019-09-12 11:01:07 +08:00
    Keppel
        1
    Keppel  
       2019-09-11 13:58:00 +08:00
    你可以试试 having
    newtype0092
        2
    newtype0092  
       2019-09-11 14:02:54 +08:00
    按理说 group by 操作后应该只使用 group by 的列,如果要用到其他列就应该在 group by 之前先排序。
    sytnishizuiai
        3
    sytnishizuiai  
    OP
       2019-09-11 14:04:50 +08:00
    @Keppel having 只能操作 group by 内的列,所以用不了
    @newtype0092 这个看过别人写,我自己试过用复合 sql,先根据 money 排序,然后 group by,还是没用
    newtype0092
        4
    newtype0092  
       2019-09-11 14:06:54 +08:00
    select * from (select * from T order by money) tmp group by city, business;
    大概这样
    newtype0092
        5
    newtype0092  
       2019-09-11 14:11:19 +08:00   ❤️ 1
    @sytnishizuiai 我自己的数据这种写法是 ok 的,你那边没用的话有没有具体的 case 啊
    dengzhaohui
        6
    dengzhaohui  
       2019-09-11 14:14:23 +08:00   ❤️ 1
    @newtype0092 MySQL5.7 优化掉了子查询内的 order by,大部分说加上 limit 或 distinct 就能强制排序,然鹅我试过并没有用 排序分组难搞
    sytnishizuiai
        7
    sytnishizuiai  
    OP
       2019-09-11 14:20:04 +08:00
    sytnishizuiai
        8
    sytnishizuiai  
    OP
       2019-09-11 14:23:01 +08:00
    @dengzhaohui 超级感谢,你一下子说中要害了,我去查了下果然好多人说(昨天关于这个问题查了好久),现在加上 limit 就可以了
    ayonel
        9
    ayonel  
       2019-09-11 14:37:26 +08:00   ❤️ 2
    新建表:
    CREATE TABLE `demo` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `city` varchar(11) DEFAULT NULL,
    `business` varchar(11) DEFAULT NULL,
    `money` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    插入以下数据:
    id city business money
    1 beijing waimai 10
    2 beijing dache 20
    3 beijing dache 30
    4 beijing waimai 40
    5 beijing waimai 20

    按照楼主的意思,需要返回的结果是:id=3 以及 id=4 的记录


    思路:自己 left join 自己,将统一分组的记录 join 一起,并且加一个过滤条件 左表的 money < 右表的 moeny, 这样的结果是所有符合该条件的关联记录中,右表都会有值。最终只需要挑右表中任意字段为 null 的记录就行了。

    sql:
    select a.* from demo a left join demo b on a.city=b.city and a.business=b.business and a.money < b.money where b.money is null;

    结果:
    3 beijing dache 30
    4 beijing waimai 40
    xux9311
        10
    xux9311  
       2019-09-11 14:39:49 +08:00   ❤️ 1
    要 group by 字段外的信息么,原来的表 join 一下吧
    大概这样
    ```sql
    select business.id, business.money from business join (select city, min(money) as min_money from business group by city) as temp on business.city = temp.city and business.money = temp.min_money;
    ```
    gz911122
        12
    gz911122  
       2019-09-11 14:43:18 +08:00
    升 8.0
    然后用开窗函数

    换 pg
    sytnishizuiai
        13
    sytnishizuiai  
    OP
       2019-09-11 14:43:27 +08:00
    @ayonel 谢谢,这个方法我看到过但没试,因为项目的表很大 本身就是 2 表关联查询,会变的更复杂就没考虑了。
    sytnishizuiai
        14
    sytnishizuiai  
    OP
       2019-09-11 14:46:29 +08:00
    @xux9311 类似的方案我试过,里面那个 select group by 的时候,得到的 min(money)和 city 其实不是匹配的
    sytnishizuiai
        15
    sytnishizuiai  
    OP
       2019-09-11 14:47:42 +08:00
    @ayonel 这个答案 5.7 不能用了,就是 4 楼的,现在加个 limit 就行
    jadec0der
        16
    jadec0der  
       2019-09-11 14:47:49 +08:00
    先排序,再 group by,用了 ls 的表和数据

    http://www.sqlfiddle.com/#!9/4aaa7f/4
    newtype0092
        17
    newtype0092  
       2019-09-11 14:49:51 +08:00
    @dengzhaohui 我的版本还是 5.6,用 5.7 试了一下,group by 语句里 select 其他字段直接报 ERROR 了。。。
    ayonel
        18
    ayonel  
       2019-09-11 14:50:31 +08:00
    @sytnishizuiai 为什么不能用,我就用的 5.7
    ayonel
        19
    ayonel  
       2019-09-11 14:54:05 +08:00
    @jadec0der 这种写法默认的 mysql 5.7 开启了 ONLY_FULL_GROUP_BY 参数,会报错。
    Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
    sytnishizuiai
        20
    sytnishizuiai  
    OP
       2019-09-11 14:55:06 +08:00
    @gz911122 我去学习下

    @jadec0der 我 5.7 就不行了

    @ayonel 不好意思,我就看了第一个,第一个不行的 我试了好几遍,第二个答案和 9 楼的想法一样,学习了
    sytnishizuiai
        21
    sytnishizuiai  
    OP
       2019-09-11 14:58:17 +08:00
    @ayonel only_full_group_by 肯定改了,你说的应该是链接内第一个答案,我 5.7.26 ,我试了 N 次了,因为我搜索到的大部分解决方案都是这个
    lolizeppelin
        22
    lolizeppelin  
       2019-09-11 15:01:05 +08:00 via Android
    统计分析请换 pg 2333
    ayonel
        23
    ayonel  
       2019-09-11 15:11:04 +08:00
    @sytnishizuiai 不明白这种写法为什么会报错,这符合标准 sql 规范。我 5.7.21
    dexterzzz
        24
    dexterzzz  
       2019-09-11 15:27:52 +08:00 via Android
    window 函数,sql 基础技术
    1runningbird
        25
    1runningbird  
       2019-09-11 16:13:55 +08:00 via iPhone
    自己 join 自己
    akira
        26
    akira  
       2019-09-11 16:31:40 +08:00
    @sytnishizuiai 10l 的方案应该是可以出正确答案的,city 和 money 不匹配的话,应该是 bussiness 那边的影响。 用 9l 的表写出来大概这样

    select demo.id, a.* from demo ,
    (
    select city,business, min(money) `min_money` from demo
    group by city, business
    ) a

    where demo.city = a.city and demo.business = a.business and demo.money = a.min_money
    sytnishizuiai
        27
    sytnishizuiai  
    OP
       2019-09-11 16:42:53 +08:00
    @akira 我项目和帖子的 demo 我都试过,获得的结果,min(money)只是额外多一列最小值(加上或去掉,其他数据都没变化),所以 MIN 的值和同行其他字段是匹配不上的,所以会导致外面的 select 也没用了。
    akira
        28
    akira  
       2019-09-11 17:07:47 +08:00
    @sytnishizuiai 你是不是漏了 group by。
    pmsg863
        29
    pmsg863  
       2019-09-11 17:27:15 +08:00
    开窗函数了解下。
    hfc
        30
    hfc  
       2019-09-11 18:00:15 +08:00
    mark
    前两天我也想做类似的处理,一直搞不定,后来直接改表结构了😂,来你这一看,原来是 5.7 之后没法那么用了...
    xux9311
        31
    xux9311  
       2019-09-11 18:27:16 +08:00
    @sytnishizuiai min(money)和 city 在 group by city 的情况下怎么会不匹配呢。可能我理解错你的问题了,(city, business)是 unique index 吧?
    robert233
        32
    robert233  
       2019-09-11 19:19:23 +08:00
    mysql 8.0 新增了窗口函数
    sytnishizuiai
        33
    sytnishizuiai  
    OP
       2019-09-12 11:00:21 +08:00
    @xux9311 我是 5.7 的,我在自己项目和帖子内的简易 demo (无索引)都试过,确实会出现匹配不上的情况
    sytnishizuiai
        34
    sytnishizuiai  
    OP
       2019-09-12 11:01:07 +08:00
    @gz911122
    @dexterzzz
    @pmsg863
    @robert233 谢谢,学习了!
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4650 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 01:07 · PVG 09:07 · LAX 17:07 · JFK 20:07
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.