MySQL ORDER BY 多列 ASC 和 DESC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13694907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL ORDER BY multiple column ASC and DESC
提问by Tan Viet
I have 2 MYSQL tables, usersand scores. Detail:
我有 2 个 MYSQL 表、用户和分数。细节：
- users table:
- 用户表：
- scores table:
- 分数表：
My intention is get 20 users list that have pointfield sort DESC (descending) combine avg_timefield sort ASC (ascending). I use the query:
我的目的是获得 20 个用户列表，这些列表具有点字段排序 DESC（降序）结合avg_time字段排序 ASC（升序）。我使用查询：
SELECT users.username, scores.point, scores.avg_time
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20
The result is:
结果是：
The result is wrong because the first line is exactly point = 100 and avg_time = 60.
结果是错误的，因为第一行正好是 point = 100 和 avg_time = 60。
My desired result is:
我想要的结果是：
username point avg_time
demo123 100 60
demo123456 100 100
demo 90 120
I tried many times with different queries but the result is still wrong. Could you give me some solutions?
我用不同的查询尝试了很多次，但结果仍然是错误的。你能给我一些解决方案吗？
Thanks in advance!
提前致谢！
采纳答案by DRapp
Ok, I THINK I understand what you want now, and let me clarify to confirm before the query. You want 1 record for each user. For each user, you want their BEST POINTS score record. Of the best points per user, you want the one with the best average time. Once you have all users "best" values, you want the final results sorted with best points first... Almost like ranking of a competition.
好的，我想我现在明白你想要什么了，让我在查询之前澄清一下。您希望每个用户有 1 条记录。对于每个用户，您需要他们的 BEST POINTS 得分记录。在每位用户的最佳点数中，您需要平均时间最佳的点数。一旦您拥有所有用户的“最佳”值，您就希望将最终结果按最佳点排序……几乎就像比赛排名一样。
So now the query. If the above statement is accurate, you need to start with getting the best point/average time per person and assigning a "Rank" to that entry. This is easily done using MySQL @ variables. Then, just include a HAVING clause to only keep those records ranked 1 for each person. Finally apply the order by of best points and shortest average time.
所以现在查询。如果上述陈述是准确的，您需要从获得每个人的最佳点/平均时间并为该条目分配“排名”开始。这可以使用 MySQL @ 变量轻松完成。然后，只需包含一个 HAVING 子句，以仅保留每个人排名 1 的记录。最后应用最佳点和最短平均时间的顺序。
select
U.UserName,
PreSortedPerUser.Point,
PreSortedPerUser.Avg_Time,
@UserRank := if( @lastUserID = PreSortedPerUser.User_ID, @UserRank +1, 1 ) FinalRank,
@lastUserID := PreSortedPerUser.User_ID
from
( select
S.user_id,
S.point,
S.avg_time
from
Scores S
order by
S.user_id,
S.point DESC,
S.Avg_Time ) PreSortedPerUser
JOIN Users U
on PreSortedPerUser.user_ID = U.ID,
( select @lastUserID := 0,
@UserRank := 0 ) sqlvars
having
FinalRank = 1
order by
Point Desc,
Avg_Time
Results as handled by SQLFiddle
Note, due to the inline @variables needed to get the answer, there are the two extra columns at the end of each row. These are just "left-over" and can be ignored in any actual output presentation you are trying to do... OR, you can wrap the entire thing above one more level to just get the few columns you want like
请注意，由于需要内联 @variables 来获得答案，因此每行末尾有两个额外的列。这些只是“遗留的”，可以在您尝试执行的任何实际输出演示中忽略...
select
PQ.UserName,
PQ.Point,
PQ.Avg_Time
from
( entire query above pasted here ) as PQ
回答by namxee
i think u miss understand about table relation..
我想你想念了解表关系..
users : scores = 1 : *
用户：分数 = 1：*
just joinis not a solution.
只是join不是解决方案。
is this your intention?
这是你的意图吗？
SELECT users.username, avg(scores.point), avg(scores.avg_time)
FROM scores, users
WHERE scores.user_id = users.id
GROUP BY users.username
ORDER BY avg(scores.point) DESC, avg(scores.avg_time)
LIMIT 0, 20
(this query to get each users average point and average avg_time by desc point, asc )avg_time
（此查询通过 desc 点，asc 获取每个用户的平均点和平均 avg_time ）avg_time
if you want to get each scores ranking? use left outer join
如果你想得到每个分数的排名？用left outer join
SELECT users.username, scores.point, scores.avg_time
FROM scores left outer join users on scores.user_id = users.id
ORDER BY scores.point DESC, scores.avg_time
LIMIT 0, 20
回答by Rad Apdal
@DRapp is a genius. I never understood how he coded his SQL,so I tried coding it in my own understanding.
@DRapp 是个天才。我从来不明白他是如何编码他的 SQL 的，所以我试着按照我自己的理解来编码。
SELECT
f.username,
f.point,
f.avg_time
FROM
(
SELECT
userscores.username,
userscores.point,
userscores.avg_time
FROM
(
SELECT
users.username,
scores.point,
scores.avg_time
FROM
scores
JOIN users
ON scores.user_id = users.id
ORDER BY scores.point DESC
) userscores
ORDER BY
point DESC,
avg_time
) f
GROUP BY f.username
ORDER BY point DESC
It yields the same result by using GROUP BY instead of the user @variables.
它通过使用 GROUP BY 而不是用户 @variables 产生相同的结果。
回答by babaoqi
group by default order by pk id，so the result
username point avg_time
demo123 100 90 ---> id = 4
demo123456 100 100 ---> id = 7
demo 90 120 ---> id = 1
group 默认按pk id排序，所以结果
用户名指向avg_time
demo123 100 90 ---> id = 4
demo123456 100 100 ---> id = 7
demo 90 120 ---> id = 1
