点滴记忆

笔记

mysql语句先排序再分组

2012-12-11 一抹阳光 数据库

在mysql中,GROUP BY和ORDER BY一块用时,ORDER是对GROUP之后的数据进行排序,如何先排序在分组呢,借助临时表或者子查询吧,实例:

SELECT * FROM (SELECT * FROM openservice WHERE `otime`+`h`*3600+`m`*60 <1355209357 ORDER BY otime DESC) AS open
LEFT JOIN archives AS arc ON arc.id = open.aid
LEFT JOIN addonspec AS spec ON spec.aid = open.aid
LEFT JOIN addon17 AS ad ON ad.aid = open.comid
WHERE open.aid = '23147'
GROUP BY ad.aid
ORDER BY open.otime DESC
LIMIT 20