mysql group by 组内排序方法

最新推荐文章于 2024-04-19 02:09:14 发布
最新推荐文章于 2024-04-19 02:09:14 发布
阅读量696 收藏
点赞数
分类专栏： 数据库
原文链接：https://blog.csdn.net/fdipzone/article/details/72453553
版权

mysql的group by语法可以根据指定的规则对数据进行分组，分组就是将一个数据集划分成若干个小区域，然后再针对若干个小区域进行数据处理。本文将介绍mysql使用group by分组时，实现组内排序的方法。

mysql的group by语法可以对数据进行分组，但是分组后的数据并不能进行组内排序。


例如一个评论表有多个用户评论，需要获取每个用户最后评论的内容。

创建测试数据表及数据

CREATE TABLE `comment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  `addtime` datetime NOT NULL,
  `lastmodify` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `addtime` (`addtime`),
  KEY `uid_addtime` (`user_id`,`addtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO comment (id, user_id, content, addtime, lastmodify) VALUES
(1, 1, ‘评论1’, ‘2017-05-17 00:00:00’, ‘2017-05-17 00:00:00’),
(2, 1, ‘评论2’, ‘2017-05-17 00:00:01’, ‘2017-05-17 00:00:01’),
(3, 2, ‘评论1’, ‘2017-05-17 00:00:02’, ‘2017-05-17 00:00:02’),
(4, 2, ‘评论2’, ‘2017-05-17 00:00:03’, ‘2017-05-17 00:00:03’),
(5, 3, ‘评论1’, ‘2017-05-17 00:00:04’, ‘2017-05-17 00:00:04’),
(6, 1, ‘评论3’, ‘2017-05-17 00:00:05’, ‘2017-05-17 00:00:05’),
(7, 4, ‘评论1’, ‘2017-05-17 00:00:06’, ‘2017-05-17 00:00:06’),
(8, 4, ‘评论2’, ‘2017-05-17 00:00:07’, ‘2017-05-17 00:00:07’),
(9, 4, ‘评论3’, ‘2017-05-17 00:00:08’, ‘2017-05-17 00:00:08’),
(10, 4, ‘评论4’, ‘2017-05-17 00:00:09’, ‘2017-05-17 00:00:09’),
(11, 3, ‘评论2’, ‘2017-05-17 00:00:10’, ‘2017-05-17 00:00:10’);

select * from comment;
+----±--------±--------±--------------------±--------------------+
| id | user_id | content | addtime | lastmodify |
+----±--------±--------±--------------------±--------------------+
| 1 | 1 | 评论1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 |
| 2 | 1 | 评论2 | 2017-05-17 00:00:01 | 2017-05-17 00:00:01 |
| 3 | 2 | 评论1 | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 |
| 4 | 2 | 评论2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 |
| 5 | 3 | 评论1 | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 |
| 6 | 1 | 评论3 | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 |
| 7 | 4 | 评论1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 |
| 8 | 4 | 评论2 | 2017-05-17 00:00:07 | 2017-05-17 00:00:07 |
| 9 | 4 | 评论3 | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 |
| 10 | 4 | 评论4 | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 |
| 11 | 3 | 评论2 | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 |
+----±--------±--------±--------------------±--------------------+

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41

在comment表中，每个用户最后评论的内容就是id为6,4,11,10的记录。

使用group by查询

select * from comment group by user_id;
+----+---------+---------+---------------------+---------------------+
| id | user_id | content | addtime             | lastmodify          |
+----+---------+---------+---------------------+---------------------+
|  1 |       1 | 评论1   | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 |
|  3 |       2 | 评论1   | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 |
|  5 |       3 | 评论1   | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 |
|  7 |       4 | 评论1   | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 |
+----+---------+---------+---------------------+---------------------+
 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

可以看到结果，分组后只会返回分组内的第一条数据。因为group by语法没有进行组内排序的功能，只会按mysql默认的排序显示。


如何才能对group by分组内的数据进行排序了，这个需要根据不同的需求处理。


1.id最大的，评论时间肯定最新

这种情况我们可以使用id代替时间去搜寻并组内排序，使用max(id)就可以获取到每个分组中最大的评论id(即最新的评论)

select * from comment where id in(select max(id) from comment group by user_id) order by user_id;
+----+---------+---------+---------------------+---------------------+
| id | user_id | content | addtime             | lastmodify          |
+----+---------+---------+---------------------+---------------------+
|  6 |       1 | 评论3   | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 |
|  4 |       2 | 评论2   | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 |
| 11 |       3 | 评论2   | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 |
| 10 |       4 | 评论4   | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 |
+----+---------+---------+---------------------+---------------------+
 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9



2.id与评论时间没有关系，id大的评论时间可能不是最新

这种情况我们就需要使用max(addtime)来获取最新的评论，但因为不同用户的评论时间有可能相同，因此还需要加多user_id这个条件去查询。

重新创建测试数据

truncate table comment;

INSERT INTO comment (id, user_id, content, addtime, lastmodify) VALUES
(1, 1, ‘评论1’, ‘2017-05-17 00:00:00’, ‘2017-05-17 00:00:00’),
(2, 1, ‘评论2’, ‘2017-05-17 00:10:01’, ‘2017-05-17 00:10:01’),
(3, 2, ‘评论1’, ‘2017-05-17 00:10:02’, ‘2017-05-17 00:10:02’),
(4, 2, ‘评论2’, ‘2017-05-17 00:00:03’, ‘2017-05-17 00:00:03’),
(5, 3, ‘评论1’, ‘2017-05-17 00:10:04’, ‘2017-05-17 00:10:04’),
(6, 1, ‘评论3’, ‘2017-05-17 00:00:05’, ‘2017-05-17 00:00:05’),
(7, 4, ‘评论1’, ‘2017-05-17 00:00:06’, ‘2017-05-17 00:00:06’),
(8, 4, ‘评论2’, ‘2017-05-17 00:10:07’, ‘2017-05-17 00:10:07’),
(9, 4, ‘评论3’, ‘2017-05-17 00:00:08’, ‘2017-05-17 00:00:08’),
(10, 4, ‘评论4’, ‘2017-05-17 00:00:09’, ‘2017-05-17 00:00:09’),
(11, 3, ‘评论2’, ‘2017-05-17 00:00:10’, ‘2017-05-17 00:00:10’);

select * from comment;
+----±--------±--------±--------------------±--------------------+
| id | user_id | content | addtime | lastmodify |
+----±--------±--------±--------------------±--------------------+
| 1 | 1 | 评论1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 |
| 2 | 1 | 评论2 | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 |
| 3 | 2 | 评论1 | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 |
| 4 | 2 | 评论2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 |
| 5 | 3 | 评论1 | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 |
| 6 | 1 | 评论3 | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 |
| 7 | 4 | 评论1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 |
| 8 | 4 | 评论2 | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 |
| 9 | 4 | 评论3 | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 |
| 10 | 4 | 评论4 | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 |
| 11 | 3 | 评论2 | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 |
+----±--------±--------±--------------------±--------------------+

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

符合条件的应该是id为2,3,5,8的记录

select a.* from comment as a right join 
(select user_id, max(addtime) as maxtime from comment where user_id is not null group by user_id) as b 
on a.user_id=b.user_id and a.addtime=b.maxtime order by a.user_id asc;

+------±--------±--------±--------------------±--------------------+
| id | user_id | content | addtime | lastmodify |
+------±--------±--------±--------------------±--------------------+
| 2 | 1 | 评论2 | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 |
| 3 | 2 | 评论1 | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 |
| 5 | 3 | 评论1 | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 |
| 8 | 4 | 评论2 | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 |
+------±--------±--------±--------------------±--------------------+

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

使用right join可以减少外层的数据集。
where user_id is not null 可以使group by user_id时使用索引。

忆水思寒
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 分组排序
bt_xxx的博客
08-27 446
比如有一个全年级成绩表（grade）： id name（姓名） score（分数） class_id（班级） 1 张三 68 1 2 李四 70 1 3 王五 85 1 4 刘六 63 2 5 陈七 90 2 ...... ...... ...... ...
SQL实现group by 分组后组内排序
weixin_30823833的博客
01-06 9092
在一个月黑风高的夜晚，自己无聊学习的SQL的时候，练习，突发奇想的想实现一个功能查询，一张成绩表有如下字段，班级ID，英语成绩，数据成绩，语文成绩如下图 实现 查询出 每个班级英语成绩最高的前两名的记录。 看起来不难的业务，做起来才知道还挺麻烦的，说白了其实就是实现分组后的组内排序，一般不思考的话我们会写出这样的语句： select top 2 English,Class...
【mysql5.7】组内分组排序
牧xin的博客
11-14 2019
组内排序取第几个..
Mysql5.7分组后生成组内排名
代码大师麦克拉瑞的博客
05-17 3385
目录背景表结构思路具体实现1.拆分步骤，按分组时间倒序排序并且获取每组前四2.拆分步骤，不使用变量按分组生成排名 背景 写一个报表sql的时候遇到一个场景，有个项目分数表格，每个项目会打多次分数，那么需要取最新的四次打分，并且行转列，mysql中我使用了最常见的行转列的方法，通过case when条件判断来转，那么这个行转列中一个必要条件就是排名了，相当于我要给每个项目的四次打分生成一个排名，类似1、2、3、4这样子。 看似很简单的一个需求确实难倒我了，所以特地写个blog记录一下。 表结构 表结构大
用order by修改group by的默认排序方式
最新发布
weixin_70280523的博客
04-19 412
666
MySQL实现分组排序
m0_56555119的博客
08-08 3255
2. 给每条数据分配序号（类似于row_number()）场景描述：如下表，需从每个分组中找到分数排名前二的用户。得到的结果如上图，显然，还需移除掉用户一和用户四。结果如上图（主要看ROWNUM字段）3. 接下来就是简单的取数了。...
mysql5.7分组排序
gdssgxf的博客
06-16 2904
mysql分组排序
mysql groupby 排序_Mysql序列（八）—— group by排序问题
weixin_39924573的博客
01-19 4162
在mysql中，group by默认会执行排序：By default, MySQL sorts GROUP BY col1, col2, ... queries as if you also included ORDER BY col1, col2, ... in the query. If you include an explicit ORDER BY clause that contains...
mysql 组内排序_mysqlgroupby组内排序_MySQL
weixin_35972981的博客
02-07 187
bitsCN.commysql group by组内排序有数据表 comments------------------------------------------------| id | newsID | comment | theTime |------------------------------------------------| 1 | 1 | ...
Mysql利用group by分组排序
09-09
在Mysql中，`GROUP BY`的使用方式与Oracle等其他数据库系统有所不同，尤其是在处理分组排序时，Mysql提供了一种独特的方法。本文将深入探讨Mysql中`GROUP BY`的分组排序功能，并通过一个实际的例子来说明其工作原理...
深度分析mysql GROUP BY 与 ORDER BY
09-10
MySQL中的`GROUP BY`和`ORDER BY`是两个非常重要的SQL子句，它们分别用于数据分组和排序。在理解这两个概念之前，首先要明白SQL的基本执行顺序：`FROM`、`WHERE`、`GROUP BY`、`HAVING`、`SELECT`、`ORDER BY`。在这...
mysql使用GROUP BY分组实现取前N条记录的方法
09-10
然后通过GROUP BY分组，HAVING子句筛选出每个组内满足条件（超过的人数少于2）的记录，并按班级和分数降序排序。 ```sql SELECT a.id, a.SName, a.ClsNo, a.Score FROM aa a LEFT JOIN aa b ON a.ClsNo = b.ClsNo ...
深入解析mysql中order by与group by的顺序问题
12-15
mysql 中order by 与group by的顺序是：selectfromwheregroup byorder by注意：group by 比order by先执行，order by不会对group by 内部进行排序，如果group by后只有一条记录，那么order by 将无效。要查出group ...
MyBatis（MySQL）分组后组内排序
warneraop的博客
01-24 7585
MySQL根据字段进行分组后组内排序
mysql5.7以上的group by 内部排序
sueong的博客
01-01 429
找出每个分组下最近一条时间的记录,同时还要进行sum操作,但是group by 只会匹配每个分组下的第一条数据(如果id自增的话,就是每个分组下最小的id),所以想到的子查询建个子表先排序再连起来,结果发现order by会在5.7版本的mysql的优化器会优化order by到外部排序,5.6的版本似乎可行,所以可以在子表中加distinct id,关闭这个特效,因为id肯定不会重复,所以确保了子表数据的完整性,也不会耽误我之后的sum操作。因为mysql优化器特定情况下会合并派生表。
mysql窗口函数分组排序_Mysql5.7版本实现row_number窗口函数的分组排序功能
weixin_28730927的博客
01-21 643
在 sql server中outer apply / cross apply 可以更高效率的实现跟row_number函数同等的功能但mysql 5.7 不仅outer apply / across apply 没有, row_number也没有. 哭 !听说mysql 8.0 版本 也可以使用row_number函数了但我们使用的是5.7版本网上的资料很多, 但感觉不够简洁明了, 我就自己写一下...
mysql使用group by实现组内排序实战
铁柱的博客
05-28 6626
一、前言 需求是：获取用户充值总数最多的前100名，并且获取最新充值的时间和充值数量 分析下: （1）首先是要获取用户充值的总数，然后进行排序取前100； （2）要获取用户的最新一次充值时间和金额，那么要以用户作为分组，然后通过聚合取最新充值时间和数量 分析之后，大概就了解了，要先group by分组，然后进行组内排序。 二、解决过程 1、先按大意写一条sql 主要字段说明： pay_time ...
mysql group by 组内排序
09-13
在MySQL中，group by语法本身不提供组内排序的功能。然而，我们可以通过一些方法来实现组内排序。下面是两种常见的方法： 方法一：使用子查询和排序 可以使用子查询来先排好序，然后再使用group by进行分组。例如，我们可以在子查询中先按照指定的排序规则对数据进行排序，然后再使用group by对结果进行分组。具体的SQL语句如下所示： SELECT * FROM ( SELECT t1.*, t2.nick_name, t2.head_portrait FROM tb_circle_post t1, tb_user t2 WHERE t1.user_id = t2.id AND t1.is_delete = 1 ORDER BY t1.create_time DESC, t1.likes DESC LIMIT 0,50 ) temp GROUP BY user_id ORDER BY create_time DESC, likes DESC LIMIT 0,3 这个SQL语句首先在子查询中对数据进行排序，然后使用group by对user_id进行分组，最后再次按照指定的排序规则对结果进行排序。这样就可以实现组内排序的效果。 方法二：使用聚合函数和子查询 另一种方法是使用聚合函数和子查询来实现组内排序。具体的SQL语句如下所示： SELECT t1.user_id, MAX(t1.create_time) AS max_create_time FROM tb_circle_post t1 GROUP BY t1.user_id ORDER BY max_create_time DESC 在这个SQL语句中，我们使用MAX函数获取每个用户的最大创建时间，然后使用group by对user_id进行分组。最后，我们再按照最大创建时间进行降序排序，从而实现了组内排序。 需要注意的是，以上两种方法适用于不同的场景，具体应根据实际需求来选择合适的方法。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值