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

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

这是一个创建于 1808 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

赐教

create_at

space_id

user_id

35 条回复 • 2019-12-12 00:15:59 +08:00

alpha2016

2019-12-11 10:47:54 +08:00

SELECT space_id,user_id, max(created_at)
FROM table
GROUP BY space_id,user_id

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

2019-12-11 10:54:13 +08:00

需求有问题吧

reidxx

2019-12-11 10:56:02 +08:00

@alpha2016 #1 这样查出来的只是 create_at 字段为最大，其它字段不是同一条记录

hiths

2019-12-11 10:58:46 +08:00

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

2019-12-11 10:59:44 +08:00

不对，时间能 max （）吗。。。。

Cooky

2019-12-11 11:00:40 +08:00 via Android

子查询呗

aragakiyuii

2019-12-11 11:01:33 +08:00

什么数据库？
group by 完了 order by create_at desc，然后取第一条就行了

reidxx

2019-12-11 11:02:06 +08:00

@hiths #5 多谢老铁，这样可以，我看看能否再简化一些

hiths

2019-12-11 11:03:24 +08:00

@reidxx 简化就是写成 where, 不过数据量大就蛋疼了点

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

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

2019-12-11 11:05:18 +08:00

@hiths #6 可以的，你上面那条 sql 暂时来看满足需求，我试试增加点数据验证一下。

@aragakiyuii #8 mysql

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

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

2019-12-11 11:11:31 +08:00

@andj4cn #15 每条记录的 create_at 基本都不一样，加入 group by 的话就相当于查全部数据了，再 order by limit 1 就只返回一条数据了

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

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

2019-12-11 11:33:39 +08:00

如果你的数据只有 space_id、user_id、create_at 的话，1 楼就能满足你的要求。如果不止这 3 个字段的话，1 楼的 sql 当做一个子查询，然后用这个结果去 join 原表，on 条件就是这 3 个字段全匹配。

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

2019-12-11 11:46:04 +08:00

如果同一 space_id, user_id 下 create_at 最大的有多条，需要返回几条？
如果只需要返回 1 条（譬如 create_at 最大，id 最小的 1 条）的话楼上有些是不满足需求的。

ChoateYao

2019-12-11 11:47:03 +08:00

我在二楼已经回答了这个问题，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

2019-12-11 12:04:54 +08:00

@ChoateYao #22 还真的是，加上那段 limit 后能查出来，不加 limit 的话，查出来的是 min(create_at) 的数据，能解释下那段 limit 子句是啥意思吗？

reidxx

2019-12-11 12:05:37 +08:00

刚好我司用的是 mariadb..

reidxx

2019-12-11 12:06:53 +08:00

@hiths #5 22 楼大佬的 sql 很简洁，并满足了需求，可参考一下

ChoateYao

2019-12-11 12:10:42 +08:00

@reidxx https://mariadb.com/kb/en/library/why-is-order-by-in-a-from-subquery-ignored/ 这里有解释

x66

2019-12-11 13:45:30 +08:00

mariadb10.2 开始支持开窗函数，如果你用的是 10.2 以后的版本，可以了解一下开窗函数

Seayon

2019-12-11 13:55:42 +08:00

这么看看 oracle 的窗口函数还真是把人养懒了，我只会用窗口函数了

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

2019-12-11 15:27:51 +08:00

@x66 #27 多谢，研究一下

p1094358629

2019-12-11 16:03:35 +08:00

写个子查询啊

Yang857

2019-12-11 16:40:49 +08:00

窗口函数 row_number 解决

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

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

2019-12-12 00:15:59 +08:00

按 SQL 标准本来就不能 select 不属于 group by 的列（在关系代数上没有意义），MySQL 原先支持这种写法只是它的一个扩展，在 v5.7.5 以后默认开启 ONLY_FULL_GROUP_BY 就不能用了。如果不用窗口函数，这个问题一般是写 self-join 或者使用变量（非声明式）。