MySQL和PostgreSQL分组后获取第一条数据
对比一下 MySQL 和 PostgreSQL 分组后获取第一条数据的写法。平时工作经常遇到一些场景,根据某个字段进行分组,获取各个分组中 id 最大的一条记录。因为之前一直是使用MySQL,而且在 MySQL 5.7 版本之前分组非常简单。后来 MySQL 升级之后,就不能直接用一条简单的查询语句获取到这些数据了。
User 表
id | age | name |
---|---|---|
1 | 10 | 张三 |
2 | 10 | 张四 |
3 | 10 | 张五 |
4 | 20 | 李六 |
5 | 20 | 李七 |
6 | 20 | 李八 |
如果要根据 age 字段对上面的数据表进行分组,分组后获取到各个组 id 最大的一条记录,期望得到的结果是
id | age | name |
---|---|---|
3 | 10 | 张五 |
6 | 20 | 李八 |
MySQL 写法(个人用法)
select * from `user` where id in (
select max(id) from `user` group by age
);
PostgreSQL 写法
select * from (
select row_number() over (partition by age order by id desc) rid, * from "user"
) t where t.rid = 1;
postgresql 的写法虽然有些复杂,但很好理解,在子查询中,通过 row_number() 函数得到了每一条数据的临时序号,每个组的序号在自己的分组内都是从1开始,它的顺序由 partition 和 order by 来确定。
另外,如果在外部没有使用 where t.id = 1 这个条件,我们会看到所有的数据都被返回了,且每条数据前面都多了一条 rid。由此也能发现,在这条子查询里面它就已经扫描了全表。
声明: 因编程语言版本更新较快,当前文章所涉及的语法或某些特性相关的信息并不一定完全适用于您当前所使用的版本,请仔细甄别。文章内容仅作为学习和参考,若有错误,欢迎指正。
-
MYSQL8 按年分组,查询出每年的数量,取每组id最大的那一个值
select a.*, b.y, b.c from article a join (
select max(id) id, y, count(*) c from (
select row_number() over (partition by FROM_UNIXTIME(ymd, '%Y') order by id desc) rid, id, FROM_UNIXTIME(ymd, '%Y') y, title, thumb from article where cid = 4
) article group by y
) b where a.id = b.id