首页   注册   登录
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 · 42 天前 · 3977 次点击
    这是一个创建于 42 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    35 回复  |  直到 2019-12-12 00:15:59 +08:00
    alpha2016
        1
    alpha2016   42 天前   ♥ 1
    SELECT space_id,user_id, max(created_at)
    FROM table
    GROUP BY space_id,user_id
    ChoateYao
        2
    ChoateYao   42 天前
    SELECT ..... ( SELECT .... ORDER BY create_at DESC) AS a GROUP BY space_id, user_id
    srx1982
        3
    srx1982   42 天前
    需求有问题吧
    reidxx
        4
    reidxx   42 天前
    @alpha2016 #1 这样查出来的只是 create_at 字段为最大,其它字段不是同一条记录
    hiths
        5
    hiths   42 天前   ♥ 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   42 天前
    不对,时间能 max ()吗。。。。
    Cooky
        7
    Cooky   42 天前 via Android
    子查询呗
    aragakiyuii
        8
    aragakiyuii   42 天前
    什么数据库?
    group by 完了 order by create_at desc, 然后取第一条就行了
    reidxx
        9
    reidxx   42 天前
    @hiths #5 多谢老铁,这样可以,我看看能否再简化一些
    hiths
        10
    hiths   42 天前
    @reidxx 简化就是写成 where, 不过数据量大就蛋疼了点
    lensan
        11
    lensan   42 天前
    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   42 天前
    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   42 天前
    @hiths #6 可以的,你上面那条 sql 暂时来看满足需求,我试试增加点数据验证一下。

    @aragakiyuii #8 mysql
    lff0305
        14
    lff0305   42 天前
    ```
    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   42 天前
    create_at 也需要 group by 吧,先 根据 space_id, user_id, create_at 进行 group by,然后 select * from () order by create_at desc limit 1
    reidxx
        16
    reidxx   42 天前
    @andj4cn #15 每条记录的 create_at 基本都不一样,加入 group by 的话就相当于查全部数据了,再 order by limit 1 就只返回一条数据了
    dofine
        17
    dofine   42 天前
    row_number() over (partition by space_id, user_id order by create_at desc)
    == 抱歉忘记好像 mysql 不支持。
    fox0001
        18
    fox0001   42 天前 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   42 天前
    如果你的数据只有 space_id、user_id、create_at 的话,1 楼就能满足你的要求。如果不止这 3 个字段的话,1 楼的 sql 当做一个子查询,然后用这个结果去 join 原表,on 条件就是这 3 个字段全匹配。
    kiracyan
        20
    kiracyan   42 天前
    这样? 其实没太看懂需求
    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   42 天前
    如果同一 space_id, user_id 下 create_at 最大的有多条,需要返回几条?
    如果只需要返回 1 条(譬如 create_at 最大,id 最小的 1 条)的话楼上有些是不满足需求的。
    ChoateYao
        22
    ChoateYao   42 天前   ♥ 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   42 天前
    @ChoateYao #22 还真的是,加上那段 limit 后能查出来,不加 limit 的话,查出来的是 min(create_at) 的数据,能解释下那段 limit 子句是啥意思吗?
    reidxx
        24
    reidxx   42 天前
    刚好我司用的是 mariadb..
    reidxx
        25
    reidxx   42 天前
    @hiths #5 22 楼大佬的 sql 很简洁,并满足了需求,可参考一下
    x66
        27
    x66   41 天前
    mariadb10.2 开始支持开窗函数,如果你用的是 10.2 以后的版本,可以了解一下开窗函数
    Seayon
        28
    Seayon   41 天前
    这么看看 oracle 的窗口函数还真是把人养懒了,我只会用窗口函数了
    wc951
        29
    wc951   41 天前
    好巧,这需求我刚做过
    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   41 天前
    @x66 #27 多谢,研究一下
    p1094358629
        31
    p1094358629   41 天前
    写个子查询啊
    Yang857
        32
    Yang857   41 天前
    窗口函数 row_number 解决
    levelworm
        33
    levelworm   41 天前
    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   41 天前
    SELECT space_id,user_id
    FROM table
    GROUP BY space_id,user_id
    having created_at = max(created_at)
    contmonad
        35
    contmonad   41 天前
    按 SQL 标准本来就不能 select 不属于 group by 的列(在关系代数上没有意义),MySQL 原先支持这种写法只是它的一个扩展,在 v5.7.5 以后 默认开启 ONLY_FULL_GROUP_BY 就不能用了。如果不用窗口函数,这个问题一般是写 self-join 或者使用变量(非声明式)。
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2454 人在线   最高记录 5168   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 31ms · UTC 05:31 · PVG 13:31 · LAX 21:31 · JFK 00:31
    ♥ Do have faith in what you're doing.