mysql先排序再分组筛选_mysql 先排序后分组

展开全部

CREATE TABLE test

(

str VARCHAR(1),

time DATETIME

);

INSERT INTO ywtg.test (str, time) VALUES ('a', '2016-08-03 16:41:37');

INSERT INTO ywtg.test (str, time) VALUES ('b', '2016-08-10 23:42:26');

INSERT INTO ywtg.test (str, time) VALUES ('a', '2016-08-13 23:42:46');

INSERT INTO ywtg.test (str, time) VALUES ('b', '2016-08-14 23:42:58');

INSERT INTO ywtg.test (str, time) VALUES ('b', '2016-08-15 23:43:13');

如上是表结构和数据,**需求:组与组之间按时间从大到小排序,组内数据按从小到大排序.**

如上数据也就是b组在a组前,b组和32313133353236313431303231363533e58685e5aeb931333361313334a组 组内数据又按从小到大排序,实现sql如下:

SELECT tt.*

FROM test tt LEFT JOIN (

SELECT

@rownum := @rownum + 1 AS rownum,

test.str

FROM (SELECT @rownum := 0) r, (SELECT str

FROM test

GROUP BY str

ORDER BY time DESC) test

) aa ON tt.str = aa.str

ORDER BY aa.rownum, time;