如图表结构,现在需要根据 space_id&user_id 做 group by,然后查询 create_at 最大的那条数据,被难倒了,有没有大佬赐教一下。
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 |
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
|
3
srx1982 2019-12-11 10:54:13 +08:00
需求有问题吧
|
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 |
6
hiths 2019-12-11 10:59:44 +08:00
不对,时间能 max ()吗。。。。
|
7
Cooky 2019-12-11 11:00:40 +08:00 via Android
子查询呗
|
8
aragakiyuii 2019-12-11 11:01:33 +08:00
什么数据库?
group by 完了 order by create_at desc, 然后取第一条就行了 |
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 |
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
|
13
reidxx OP |
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; ``` 没试验,大概这个意思,效率不高 |
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
|
16
reidxx OP @andj4cn #15 每条记录的 create_at 基本都不一样,加入 group by 的话就相当于查全部数据了,再 order by limit 1 就只返回一条数据了
|
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 不支持。 |
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 |
19
xuanbg 2019-12-11 11:33:39 +08:00
如果你的数据只有 space_id、user_id、create_at 的话,1 楼就能满足你的要求。如果不止这 3 个字段的话,1 楼的 sql 当做一个子查询,然后用这个结果去 join 原表,on 条件就是这 3 个字段全匹配。
|
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 ) |
21
surfire91 2019-12-11 11:46:04 +08:00
如果同一 space_id, user_id 下 create_at 最大的有多条,需要返回几条?
如果只需要返回 1 条(譬如 create_at 最大,id 最小的 1 条)的话楼上有些是不满足需求的。 |
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 |
23
reidxx OP @ChoateYao #22 还真的是,加上那段 limit 后能查出来,不加 limit 的话,查出来的是 min(create_at) 的数据,能解释下那段 limit 子句是啥意思吗?
|
24
reidxx OP 刚好我司用的是 mariadb..
|
26
ChoateYao 2019-12-11 12:10:42 +08:00
|
27
x66 2019-12-11 13:45:30 +08:00
mariadb10.2 开始支持开窗函数,如果你用的是 10.2 以后的版本,可以了解一下开窗函数
|
28
Seayon 2019-12-11 13:55:42 +08:00
这么看看 oracle 的窗口函数还真是把人养懒了,我只会用窗口函数了
|
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 |
31
p1094358629 2019-12-11 16:03:35 +08:00
写个子查询啊
|
32
Yang857 2019-12-11 16:40:49 +08:00
窗口函数 row_number 解决
|
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 |
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) |
35
contmonad 2019-12-12 00:15:59 +08:00
按 SQL 标准本来就不能 select 不属于 group by 的列(在关系代数上没有意义),MySQL 原先支持这种写法只是它的一个扩展,在 v5.7.5 以后 默认开启 ONLY_FULL_GROUP_BY 就不能用了。如果不用窗口函数,这个问题一般是写 self-join 或者使用变量(非声明式)。
|