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

有个需求不知道怎么写 SQL,特来请教一下

  •  
  •   reidxx · 2019-12-11 10:36:28 +08:00 · 6222 次点击
    这是一个创建于 1570 天前的主题,其中的信息可能已经有所发展或是发生改变。

    如图表结构,现在需要根据 space_id&user_id 做 group by,然后查询 create_at 最大的那条数据,被难倒了,有没有大佬赐教一下。

    35 条回复    2019-12-12 00:15:59 +08:00
    alpha2016
        1
    alpha2016  
       2019-12-11 10:47:54 +08:00   ❤️ 1
    SELECT space_id,user_id, max(created_at)
    FROM table
    GROUP BY space_id,user_id
    ChoateYao
        2
    ChoateYao  
       2019-12-11 10:49:01 +08:00
    SELECT ..... ( SELECT .... ORDER BY create_at DESC) AS a GROUP BY space_id, user_id
    srx1982
        3
    srx1982  
       2019-12-11 10:54:13 +08:00
    需求有问题吧
    reidxx
        4
    reidxx  
    OP
       2019-12-11 10:56:02 +08:00
    @alpha2016 #1 这样查出来的只是 create_at 字段为最大,其它字段不是同一条记录
    hiths
        5
    hiths  
       2019-12-11 10:58:46 +08:00   ❤️ 1
    SELECT
    t2.*
    FROM
    ( SELECT space_id, user_id, max( create_at ) AS mx FROM TABLE GROUP BY space_id, user_id ) AS t1
    LEFT JOIN TABLE AS t2 ON t2.space_id = t1.space_id
    AND t2.user_id = t1.user_id
    AND t2.create_at = t1.mx
    hiths
        6
    hiths  
       2019-12-11 10:59:44 +08:00
    不对,时间能 max ()吗。。。。
    Cooky
        7
    Cooky  
       2019-12-11 11:00:40 +08:00 via Android
    子查询呗
    aragakiyuii
        8
    aragakiyuii  
       2019-12-11 11:01:33 +08:00
    什么数据库?
    group by 完了 order by create_at desc, 然后取第一条就行了
    reidxx
        9
    reidxx  
    OP
       2019-12-11 11:02:06 +08:00
    @hiths #5 多谢老铁,这样可以,我看看能否再简化一些
    hiths
        10
    hiths  
       2019-12-11 11:03:24 +08:00
    @reidxx 简化就是写成 where, 不过数据量大就蛋疼了点
    lensan
        11
    lensan  
       2019-12-11 11:04:18 +08:00
    select max (created_at) ,space_id,user_id
    from table t
    join (
    space_id,user_id from group by space_id,user_id
    ) t2 on t.space_id=t2.space_id and t.user_id =t2.user_id
    zhesheng
        12
    zhesheng  
       2019-12-11 11:05:04 +08:00
    select * from ( select a.*,row_number() over (partition by a.space_id,a.user_id order by a.create_at desc) rn from table_name a ) where rn =1
    reidxx
        13
    reidxx  
    OP
       2019-12-11 11:05:18 +08:00
    @hiths #6 可以的,你上面那条 sql 暂时来看满足需求,我试试增加点数据验证一下。

    @aragakiyuii #8 mysql
    lff0305
        14
    lff0305  
       2019-12-11 11:08:40 +08:00
    ```
    select * from table as t
    where (select count(distinct(e1.created_at)) from table as t1 where t1.space_id= e.space_id and t1.user_id
    = t.user_id and t1.created_at > t.created_at) < 1;
    ```

    没试验,大概这个意思,效率不高
    andj4cn
        15
    andj4cn  
       2019-12-11 11:09:45 +08:00
    create_at 也需要 group by 吧,先 根据 space_id, user_id, create_at 进行 group by,然后 select * from () order by create_at desc limit 1
    reidxx
        16
    reidxx  
    OP
       2019-12-11 11:11:31 +08:00
    @andj4cn #15 每条记录的 create_at 基本都不一样,加入 group by 的话就相当于查全部数据了,再 order by limit 1 就只返回一条数据了
    dofine
        17
    dofine  
       2019-12-11 11:14:30 +08:00
    row_number() over (partition by space_id, user_id order by create_at desc)
    == 抱歉忘记好像 mysql 不支持。
    fox0001
        18
    fox0001  
       2019-12-11 11:20:16 +08:00 via Android
    如果是 SQL server,我会用 row_number。具体如下

    select space_id, user_i
    from(
    select space_id, user_id, row_number()over(partition by space_id, user_id order by create_at desc) num
    from .....
    )a
    where number=1
    xuanbg
        19
    xuanbg  
       2019-12-11 11:33:39 +08:00
    如果你的数据只有 space_id、user_id、create_at 的话,1 楼就能满足你的要求。如果不止这 3 个字段的话,1 楼的 sql 当做一个子查询,然后用这个结果去 join 原表,on 条件就是这 3 个字段全匹配。
    kiracyan
        20
    kiracyan  
       2019-12-11 11:36:37 +08:00
    这样? 其实没太看懂需求
    select * from t1
    where not exists(select 1 from t2 where t1.user_id=t2.user_id and t1.space_id=t2.space_id and t1.create_at <t2.create_at )
    surfire91
        21
    surfire91  
       2019-12-11 11:46:04 +08:00
    如果同一 space_id, user_id 下 create_at 最大的有多条,需要返回几条?
    如果只需要返回 1 条(譬如 create_at 最大,id 最小的 1 条)的话楼上有些是不满足需求的。
    ChoateYao
        22
    ChoateYao  
       2019-12-11 11:47:03 +08:00   ❤️ 2
    我在二楼已经回答了这个问题,MySQL 的 Group By 特性就是根据数据默认排序,取去重数据的第一条。

    根据这个特性,我们先对数据进行排序,然后在 Group By 即可得到你想要的结果。

    但是在 MariaDB 中,该方法并不起效果,需要额外引入一个莫名其妙的语句: LIMIT 18446744073709551615

    所以最终的 SQL 是: SELECT ..... ( SELECT .... ORDER BY create_at DESC LIMIT 18446744073709551615) AS a GROUP BY space_id, user_id
    reidxx
        23
    reidxx  
    OP
       2019-12-11 12:04:54 +08:00
    @ChoateYao #22 还真的是,加上那段 limit 后能查出来,不加 limit 的话,查出来的是 min(create_at) 的数据,能解释下那段 limit 子句是啥意思吗?
    reidxx
        24
    reidxx  
    OP
       2019-12-11 12:05:37 +08:00
    刚好我司用的是 mariadb..
    reidxx
        25
    reidxx  
    OP
       2019-12-11 12:06:53 +08:00
    @hiths #5 22 楼大佬的 sql 很简洁,并满足了需求,可参考一下
    ChoateYao
        26
    ChoateYao  
       2019-12-11 12:10:42 +08:00
    x66
        27
    x66  
       2019-12-11 13:45:30 +08:00
    mariadb10.2 开始支持开窗函数,如果你用的是 10.2 以后的版本,可以了解一下开窗函数
    Seayon
        28
    Seayon  
       2019-12-11 13:55:42 +08:00
    这么看看 oracle 的窗口函数还真是把人养懒了,我只会用窗口函数了
    wc951
        29
    wc951  
       2019-12-11 14:53:49 +08:00
    好巧,这需求我刚做过
    select t1.* from table t1,
    (select space_id,user_id,max(create_at) as maxtime from table group by space_id,user_id) t2
    where t1.space_id=t2.space_id and t1.user_id=t2.user_id and t1.create_at=t2.maxtime
    reidxx
        30
    reidxx  
    OP
       2019-12-11 15:27:51 +08:00
    @x66 #27 多谢,研究一下
    p1094358629
        31
    p1094358629  
       2019-12-11 16:03:35 +08:00
    写个子查询啊
    Yang857
        32
    Yang857  
       2019-12-11 16:40:49 +08:00
    窗口函数 row_number 解决
    levelworm
        33
    levelworm  
       2019-12-11 22:30:10 +08:00
    SELECT
    t.*
    FROM (
    SELECT
    t.*,
    MAX(create_at) OVER(PARTITION BY space_id, user_id) AS max_create_at
    FROM
    table AS t
    ) AS a
    INNER JOIN table AS t
    ON t.space_id = a.space_id AND t.user_id = a.user_id AND t.create_at = a.max_create_at

    或者楼上说的窗口函数更简单
    SELECT
    t.*
    FROM (
    SELECT
    t.*,
    ROW_NUMBER() OVER(PARTITION BY space_id, user_id ORDER BY create_at DESC) AS row_num
    FROM
    table AS t
    ) AS a
    WHERE
    row_num = 1
    ricky077
        34
    ricky077  
       2019-12-11 22:43:32 +08:00
    SELECT space_id,user_id
    FROM table
    GROUP BY space_id,user_id
    having created_at = max(created_at)
    contmonad
        35
    contmonad  
       2019-12-12 00:15:59 +08:00
    按 SQL 标准本来就不能 select 不属于 group by 的列(在关系代数上没有意义),MySQL 原先支持这种写法只是它的一个扩展,在 v5.7.5 以后 默认开启 ONLY_FULL_GROUP_BY 就不能用了。如果不用窗口函数,这个问题一般是写 self-join 或者使用变量(非声明式)。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2885 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 14:20 · PVG 22:20 · LAX 07:20 · JFK 10:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.