MySQL ORDER BY多列ASC和DESC

Question

我有2个MYSQL表，用户和分数。详细信息：

• 用户表：

• 得分表：

我的目的是获得20个用户列表，这些用户具有点域排序DESC（降序）和平均时间域排序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

结果是：

结果是错误的，因为第一行正好是point=100，avg_time=60。

我期望的结果是：

username    point    avg_time
demo123      100        60
demo123456   100       100
demo         90        120

我尝试了很多次不同的查询，但结果仍然是错误的。你能给我一些解决方案吗？

提前谢谢！

Answer 1

@德拉普是个天才。我一直不明白他是如何编写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

通过使用GROUP by而不是user@变量，可以得到相同的结果。

Answer 2

我想你错过了对餐桌关系的理解...

用户：分数=1:*

只是加入不是一个解决方案。

这是你的意图吗？

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

（此查询通过desc point， asc获取每个用户的平均值和平均值avg_time）avg_time

如果你想得到每个分数的排名？使用左外连接

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

Answer 3

好的，我想我现在明白你想要什么了，让我在查询前澄清一下。你想要每个用户有1条记录。对于每个用户，你想要他们的最佳积分记录。在每个用户的最佳积分中，你想要平均时间最好的。一旦你有了所有用户的“最佳”值，你希望最终结果首先以最佳积分排序......几乎就像比赛的排名一样。

现在是查询。如果上面的语句是准确的，你需要从获得每个人的最佳点/平均时间开始，并为该条目分配一个“排名”。这很容易使用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

由SQLFiddle处理的结果

注意，由于需要内联@variables来获得答案，因此每行末尾都有两个额外的列。这些只是“遗留下来的”，在任何实际的输出演示中都可以忽略。。。或者，您可以将整个内容包装到一个更高的级别上，只获得您想要的几个列

select 
      PQ.UserName,
      PQ.Point,
      PQ.Avg_Time
   from
      ( entire query above pasted here ) as PQ