Mysql uses group by grouping sort

Yesterday, there was a need to re-rank the data of the database. The data with the highest score, the shortest time and the earliest participation was sorted with one user.We can take advantage of the group by feature in MySQL.

group by for MySQL is different from Oracle in that fields can be queried without writing aggregate functions, and the result of the query is the first row of records for each group.

With the above features, mysql can be used to achieve a unique sorting.

First order by is grouped by a field, then the ordered tables are grouped so that the members of each group are ordered, and mysql gets the first row of the grouping by default.The result is the highest value for each group.

select id, (@rowno := @rowno + 1) as rank,
 score,
 (C.end_time - C.start_time) as timeConsuming,
 start_time,
 real_name,
 tel,
 expiry_code
 from (SELECT *
  FROM (select *
   from t_q_order B
   where B.score > 0
   and B.tel IS NOT NULL
   order by B.score desc,
    (B.end_time - B.start_time) asc,
    B.start_time asc) as A
  group by A.tel
  ORDER BY A.score desc,
   (A.end_time - A.start_time) asc,
   A.start_time asc) 
 as C,
 (select @rowno := 0) t
 where (C.end_time - C.start_time) > 5 limit 0,50;