MySQL的子查询中排序

起因
解决方法
参考文献

起因

有一张表保存着用户对于文件的阅读记录, 包括文件名, 读者, 阅读时间, 时长, 备注评论.

create table reading_record (
	id int primary key auto_increment,	-- 自增主键
	file varchar(255),	-- 阅读文件名
	user varchar(255),	-- 读者
	expend int,		-- 阅读时长
	time datetime,		-- 阅读时间
	note text		-- 备注评论
)

有个要求是:想要统计每个人对于每个文件的阅读总时长, 阅读次数, 最后阅读时间, 最后的阅读评论

一开始我写了下面的sql去实现这个功能:

select sum(expend) as 阅读总时长, count(id) as 阅读次数, time as 最后阅读时间, note as 最后的阅读评论 from reading_record group by user, file order by id desc;

得到的结果里面阅读总时长和阅读次数时符合要求的, 但是最后阅读时间和最后的阅读评论出现了多条错误的信息

为了证明这个sql写的有问题, 我使用下面的这个sql去进行校验.

select max(id) as 实际的每组最后一次记录id, id as 以为的每组最后一次记录id, sum(expend) as 阅读总时长, count(id) as 阅读次数, time as 最后阅读时间, note as 最后的阅读评论 from reading_record group by user, file order by id desc;

查询结果中出现多条实际的每组最后一次记录id!=以为的每组最后一次记录id.

解决方法

使用组内排序

网上搜索之后发现, 排序的order by是在分组group by之后. 所以首先要做组内排序, 然后再进行分组才能得到正确的结果.

基于上面的方法, 写了下面的sql.

select sum(expend) as 阅读总时长, count(id) as 阅读次数, time as 最后阅读时间, note as 最后的阅读评论 from (select * from reading_record order by id desc) as temptable group by user, file

结果发现:子排序中不管是以desc方式排序还是以asc方式排序, 结果都是一样的

为了排除错误, 我在查询结果字段前面都加上了temptable. 如max(temptable.id), 发现并没有什么作用.

mysql以及mariadb的特性

查了很长时间之后, 终于在一个年限比较新的文章里看到了: 新版本的MySQL或者mariadb会自动忽略subquery的排序

具体的MySQL版本我记不清了.

为了验证这个说法, 我使用了以下的sql:

-- 以id逆序输出
select * from (select * from reading_record order by id desc) as temptable;

-- 以id正序输出
select * from (select * from reading_record order by id) as temptable;

经过验证发现结果仍然是id正序输出的记录, 所以我这个版本的MySQL会忽略subquery的排序.

解决subquery忽略排序

为了实现subquery可以排序, 添加limit限制.

通过给subquery的最后加上limit限制来实现subquery的排序.

-- 为了让子查询可以查到所有的结果, 所以把limit的值设置到非常大
select * from (select * from reading_record order by id desc limit 0, 154785445745657996) as temptable;

以上解决了所有的问题, 所欲最终通过下面的sql实现了我最开始的需求.

select sum(expend) as 阅读总时长, count(id) as 阅读次数, time as 最后阅读时间, note as 最后的阅读评论 from (select * from reading_record order by id desc limit 0, 154785445745657996) as temptable group by user, file

参考文献

( SQL优化：组内排序取最大值 )[http://blog.itpub.net/29730827/viewspace-2136118/]
(mysql group by 组内排序)[http://blog.csdn.net/shellching/article/details/8292338]