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组和a组 组内数据又按从小到大排序,实现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

update table_name set title='上等' where exploit in ( select exploit from table_name order by sortid limit 0,10))

update table_name set title='中等' where exploit in ( select exploit from table_name order by sortid limit 11,40))

update table_name set title='低等' where exploit in ( select exploit from table_name order by sortid limit 51,50))

--不是以上三个级别的，你就改为下等好了。

---