mysql取出每个分组中最新的记录

转载 2016年08月30日 14:52:58

原文:深度分析mysql GROUP BY 与 ORDER BYmysql取出每个分组中最新的记录mysql 分组取最新的一条记录(整条记录)

1、建表、插入测试数据

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `category_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert  into `test`(`id`,`name`,`category_id`,`date`) values (1,'aaa',1,'2010-06-10 19:14:37'),(2,'bbb',2,'2010-06-10 19:14:55'),(3,'ccc',1,'2010-06-10 19:16:02'),(4,'ddd',1,'2010-06-10 19:16:15'),(5,'eee',2,'2010-06-10 19:16:35');

这里写图片描述

我现在需要取出每个分类中最新的内容
select * from test group by category_id order by date
结果如下
这里写图片描述
明显。这不是我想要的数据,原因是msyql已经的执行顺序是:

写的顺序:select … from… where…. group by… having… order by..
执行顺序:from… where…group by… having…. select … order by…

所以在order by拿到的结果里已经是分组的完的最后结果。

由from到where的结果如下的内容:
这里写图片描述

到group by时就得到了根据category_id分出来的多个小组
这里写图片描述

这里写图片描述

到了select的时候,只从上面的每个组里取第一条信息结果会如下
这里写图片描述

即使order by也只是从上面的结果里进行排序。并不是每个分类的最新信息。

2、方法

下面介绍两种方法,一种是通过子查询,一种是通过group_concat函数来实现。

2.1 子查询解决方案

先将数据按照日期倒序排序(日期最新的在最前面),然后在group,这样每个分类的第一条肯定是日期最新的。

select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc

2.2 通过group_concat函数

①group_concat函数

group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )

作用:将要连接的字段按照排序字段的顺序用分隔符连起来显示,默认分隔符是”,”。
如:select group_concat(id order bydatedesc) fromtestgroup by category_id

按照时间排序将id连接起来,第一个一定是时间最新的。
这里写图片描述
②substring_index

substring_index(str,delim,count),str:要处理的字符串、delim:分隔符、count:计数

例子:
  SELECT SUBSTRING_INDEX('www.test.com','.',1);
  结果是:www
  SELECT SUBSTRING_INDEX('www.test.com','.',2)
  结果是:www.test
  SELECT SUBSTRING_INDEX('www.test.com','.',-2);
  结果为:test.com
也就是说,如果count是正数,那么就是从左往右数,第N个分隔符的左边的全部内容,相反,如果是负数,那么就是从右边开始数,第N个分隔符右边的所有内容。
③解析
GROUP_CONCAT将group by 后的id排序后连接起来,SUBSTRING_INDEX取得每行(每个分类)的第一个。

SELECT * FROM `test` WHERE id IN(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY `date` DESC),',',1) FROM `test` GROUP BY category_id ) ORDER BY `date` DESC;

这里写图片描述

2、3 其他方法

一般id越大的时间越近,可以先选出各个分类最大的id,然后in。

SELECT MAX(id) AS id,category_id,MAX(DATE) FROM test GROUP BY category_id;  
SELECT * FROM test WHERE id IN (SELECT MAX(id) FROM test GROUP BY category_id);

连表,选择出每个分类的id和最大时间作为一个临时表,然后原表和临时表连接,条件是分类id和时间相等。

SELECT * FROM test AS a, 
(SELECT category_id, MAX(DATE) AS `date` FROM test AS b GROUP BY category_id)
AS b WHERE a.category_id=b.category_id AND a.date = b.date  

mysql 分组取最新的一条记录(整条记录)

mysql取分组后最新的一条记录,下面两种方法.一种是先筛选 出最大和最新的时间,在连表查询.一种是先排序,然后在次分组查询(默认第一条),就是最新的一条数据了   资料来源:http://...
  • ying890
  • ying890
  • 2015年12月22日 11:20
  • 4126

mysql取出每个分组中最新的记录

mysql的gruopby分组功能没有排序功能,所以我们如果想取出某个分组下的最新记录是不太容易的,下面介绍两种方法,一种是通过子查询,一种是通过group_concat函数来实现。 一、表结构...
  • swazer_z
  • swazer_z
  • 2017年04月11日 15:41
  • 3107

SQL分组查询,结果只取最新一条记录

select a.* from (select * from TABLE order by create_time desc) a group by a.user_id这里查询的是USER_ID相同的...
  • ydk888888
  • ydk888888
  • 2017年08月10日 10:26
  • 3061

mysql取出每个分组中最新的记录

mysql取出每个分组中最新的记录
  • t_1007
  • t_1007
  • 2016年08月30日 14:52
  • 375

MySQL 查询分页数据中分组后取每组的前N条记录

在使用数据库查询的时候,如果遇到对分页的数据分组,取每组的前N条,实际就是两次分页,先分页,在对分组的每组排序分页。SQL 如下select a.* from ( select t1.*,(sele...
  • fun913510024
  • fun913510024
  • 2015年07月29日 18:26
  • 3434

mysql 取每个分组中时间最近的记录

SELECT * FROM baoma_auction AS a WHERE NOT EXISTS ( SELECT 1 ...
  • weixin_37874819
  • weixin_37874819
  • 2017年11月17日 17:36
  • 179

mysql分组取每组前几条记录(排序)

[sql] view plaincopy CREATE TABLE `mygoods` (     `goods_id` int(11) unsigned NOT NULL AUTO_...
  • come_on_air
  • come_on_air
  • 2017年06月07日 18:02
  • 2757

mysql分组查询最新数据

select user_id,sbp,dbp,pulse from p_bp as a, (select max(id) as id, max(measure_at) as measure_at fr...
  • u011816231
  • u011816231
  • 2016年06月28日 16:42
  • 15197

先按照某字段分组然后再取每组中的前两条

From: http://blog.chinaunix.net/uid-26729093-id-4294287.html 请参考:http://bbs.csdn.net/topics/33002...
  • jjl123jjl123
  • jjl123jjl123
  • 2017年03月28日 09:42
  • 176

MySql中取出每个分组中的前N条记录

需求是这样的(CSDN上的一个问题):mysql中有个表:article(字段:id,type,date),type有1-10,10种类型。现在要用SQL找出每种类型中时间最新的前N个数据组成的集合。...
  • hudie1234567
  • hudie1234567
  • 2011年03月18日 10:59
  • 12246
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql取出每个分组中最新的记录
举报原因:
原因补充:

(最多只允许输入30个字)