mysql 在分组之前排序

发布于 2024-11-06 00:12:26 字数 526 浏览 0 评论 0原文

我认为这是最佳解决方案。但是，这个查询并没有解决我的问题 - 我有这样的表：

+--+-------+-----+
|id|user_id|score|
+--+-------+-----+
|1 |1      |5    |
+--+-------+-----+
|2 |1      |16   |
+--+-------+-----+
|3 |1      |15   |
+--+-------+-----+

查询：

  SELECT *
    FROM (`_scorboard`)
GROUP BY `user_id`
  HAVING `score` = MAX(score)
ORDER BY `score` desc 

result 0 rows

为什么它返回 0 条记录？

原文

I think this is the best solution. However, this query is not solve my issue - I have like this table:

+--+-------+-----+
|id|user_id|score|
+--+-------+-----+
|1 |1      |5    |
+--+-------+-----+
|2 |1      |16   |
+--+-------+-----+
|3 |1      |15   |
+--+-------+-----+

Query:

  SELECT *
    FROM (`_scorboard`)
GROUP BY `user_id`
  HAVING `score` = MAX(score)
ORDER BY `score` desc 

result 0 rows

Why is it returns 0 records ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初与友歌 2024-11-13 00:12:26

使用：

SELECT a.*
  FROM SCOREBOARD a
  JOIN (SELECT t.user_id,
               MAX(t.score) AS max_score
          FROM SCOREBOARD t
      GROUP BY t.user_id) b ON b.max_score = a.score
                           AND b.user_id = a.user_id

如果您想要表中得分最高的人：

SELECT a.*
  FROM SCOREBOARD a
  JOIN (SELECT MAX(t.score) AS max_score
          FROM SCOREBOARD t) b ON b.max_score = a.score

Use:

SELECT a.*
  FROM SCOREBOARD a
  JOIN (SELECT t.user_id,
               MAX(t.score) AS max_score
          FROM SCOREBOARD t
      GROUP BY t.user_id) b ON b.max_score = a.score
                           AND b.user_id = a.user_id

If you want those who have the highest score in the table:

SELECT a.*
  FROM SCOREBOARD a
  JOIN (SELECT MAX(t.score) AS max_score
          FROM SCOREBOARD t) b ON b.max_score = a.score

回复收藏 0 原文

饭团 2024-11-13 00:12:26

由于查询中有一个 GROUP BY 子句，MySQL 首先按 1 的 user_id 进行分组，选择它喜欢的任何行。然后，HAVING 子句将应用于这些选定的行。由于所选行可能是也可能不是 MAX 值为 score 的行，因此查询返回 0 个结果。

正确的做法是：

  SELECT _scoreboard.*
    FROM _scoreboard JOIN (SELECT user_id, MAX(score)
                           FROM _scorboard
                           GROUP BY user_id)
      AS t ON _scoreboard.user_id = t.user_id
          AND _scoreboard.score = t.score
ORDER BY _scoreboard.score DESC

Since you have a GROUP BY clause in your query, MySQL groups by the user_id of 1 first, choosing any of the rows that it pleases. The HAVING clause then applies to these selected rows. Since the selected row may or may not be the one with the MAX value of score, the query is returning 0 results.

The correct way to do is:

  SELECT _scoreboard.*
    FROM _scoreboard JOIN (SELECT user_id, MAX(score)
                           FROM _scorboard
                           GROUP BY user_id)
      AS t ON _scoreboard.user_id = t.user_id
          AND _scoreboard.score = t.score
ORDER BY _scoreboard.score DESC

回复收藏 0 原文

~没有更多了~