Mysql uses group by for group sorting and mysqlgroupby for group sorting
Yesterday, I had a need to deduplicate and rank the database data. The same user had the highest score, the lowest time, and the earliest data to participate in the activity. We can use the group by feature in MySQL.
The group by function of MySQL is different from that of Oracle. You do not need to write an aggregate function to query the fields. The query result is the first row record of each group.
Using the above features, mysql can be used to achieve a unique sorting;
First, order by is performed based on a field, and then an ordered table is grouped. In this way, the members in each group are ordered, and mysql obtains the first row of the Group by default. To obtain the maximum value of 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. tel order by. score desc, (. end_time-. start_time) asc,. start_time asc) as C, (select @ rowno: = 0) t where (C. end_time-C. start_time)> 5 limit 0, 50;
The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.