mysql : ORDER BY Twice

Question

已编辑:我需要用户按其全局进度 [order by avg(progression) DESC] 以及按进度排序的详细信息行进行排序。

示例:

USER 1 campaign 1 progression 100 
USER 1 campaign 2 progression 100
USER 2 campaign 1 progression 95 
USER 2 campaign 2 progression 80
USER 3 campaign 1 progression 70 
USER 3 campaign 2 progression 25

我运行以下查询:

SELECT p.usersid, p.current_campaign, p.campaign_progression
FROM progression_campagne p 
JOIN USER u ON  p.usersid=u.id AND u.organismsid=10 
GROUP BY p.usersid, p.current_campaign
ORDER BY p.usersid DESC,p.campaign_progression DESC;

要得到这样的结果:

但是我得到了以下结果:

有什么帮助吗？

Answer 1

添加平均列并将其用作第一个排序列

drop table if exists t;
create table t(user int, campaign int , progression int);
insert into t values
(1 , 1 , 100) ,
(1 , 2 , 100),
(2 , 1 , 95 ),
(2 , 2 , 80),
(3 , 1 , 70 ),
(3 , 2 , 25);

select user,campaign, progression, (select avg(t1.progression) from t t1 where t1.user = t.user group by t1.user) average
from t
order by average desc,user desc,progression desc;

+------+----------+-------------+----------+
| user | campaign | progression | average  |
+------+----------+-------------+----------+
|    1 |        1 |         100 | 100.0000 |
|    1 |        2 |         100 | 100.0000 |
|    2 |        1 |          95 |  87.5000 |
|    2 |        2 |          80 |  87.5000 |
|    3 |        1 |          70 |  47.5000 |
|    3 |        2 |          25 |  47.5000 |
+------+----------+-------------+----------+