id user score do_time
1 a 1 1
2 a 3 1
3 a 3 3
4 b 0 1
5 b 4 4
6 b 4 1
需求是 查詢每個用戶的最好成績，當score一樣時，取dotime時間最小的記錄
uj5u.com熱心網友回復：
來個大神解決一下吧，萬分感謝
uj5u.com熱心網友回復：
分兩步第一步 取相同用戶，分數一樣的時間最大
create table test select user,score,max(do_time) from tb group by user,score;
第二部取相同用戶分數大的
select * from test A where not exists (select 1 from test B where A.user=B.user and A.score<B.score);
uj5u.com熱心網友回復：
select user,max(score)as score,min(do_time)as do_time
from a
group by `user`
uj5u.com熱心網友回復：
select t.* from (select * from mytable order by score desc, do_time asc) t group by t.useruj5u.com熱心網友回復：
SELECT ID,USER,SCORE,DO_TIME FROM (SELECT ID,USER,SCORE,DO_TIME FROM SCORE ORDER BY SCORE DESC,DO_TIME ASC) GROUP BY USER這個是最簡單的寫法。
uj5u.com熱心網友回復：
select user, max(score),do_time from test_gyyGROUP BY `user`
ORDER BY do_time desc
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/93774.html
標籤：MySQL