最近在项目中遇到了一个需要先排序,再group by的问题。
上表
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`install` int(10) unsigned NOT NULL,
`day` int(10) unsigned NOT NULL,
`aid` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*Data for the table `test` */
insert into `test`(`id`,`install`,`day`,`aid`) values (1,1232,20080808,1),(2,2321,20080809,2),(3,1236,20080810,3),(5,4212,20080809,1),(6,2312,20080810,1),(7,1432,20080811,1),(8,2421,20080808,2),(9,4245,20080811,2),(10,5654,20080810,2),(11,412,20080808,3);
发现网上大部分的方法是这样的
SELECT * FROM (SELECT * FROM test ORDER BY DAY DESC) ssss GROUP BY aid ORDER BY DAY DESC
该方法在mysql5.6.17上测了没有问题,但是在新版本的mysql上测就不对,我现有的版本是10.0.13-mariaDB测了不对, 对应的版本的mysql测了也有问题。
正确的结果
错误的结果
比较了一下发现子查询中的ORDER BY DAY DESC根本没生效。
explain一下sql语句发现mysql5.6.17下打印了2条记录
但是在10.0.13-mariaDB下面只打印了后面一条记录,怀疑是不是新版的mysql对这种子查询进行了优化。
既然mysql不支持只能换方法了
第一种方法(网上了)
SELECT A.* FROM test A,
(SELECT aid, MAX(DAY) max_day FROM test GROUP BY aid) B
WHERE A.aid = B.aid AND A.day = B.max_day
ORDER BY a.day DESC
第二种方法
SELECT * FROM `test` WHERE id IN(
SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY `day` DESC),',',1) FROM `test` GROUP BY aid )
ORDER BY `day` DESC
总结:上面的表的数据是从网上复制的,对于开头写的那种查询方法不行的原因,具体的我还不清楚,只是猜测。所以遇到问题基本用的后面的方法。