如何在MySQL中选择唯一的行，然后选择具有最高值的行？

发布于 2024-11-25 23:08:46 字数 561 浏览 1 评论 0原文

我有一个 MySQL 数据库表，其中包含有关地点的信息。我试图获取所有独特的类别，然后获取评分最高的地点，但服务器返回的结果似乎不准确。

在数据库中，某一类别中得分 100 的记录可能很少，但 MySQL 会选择得分为 95 的记录。

这是一个查询：

SELECT category, score, title
FROM places 
WHERE active = '1'
GROUP BY category
ORDER BY score DESC

是否可以在单个查询中执行此操作？

更新

我已经按照建议使用 MySQL 函数 MAX() 重写了查询，但是返回的结果仍然错误，

这是新查询的示例

SELECT category, MAX(score) AS maxScore, title, score AS realScore
FROM places 
WHERE active = '1'
GROUP BY category
ORDER BY score DESC

原文

I have a MySQL database table containing information about places.
I'm trying to fetch all unique categories followed by place with highest rating, but results returned by server does not seems to be accurate.

In database in one category can be few records scored 100 but MySQL would select one that have score 95 for example.

Here is a query:

SELECT category, score, title
FROM places 
WHERE active = '1'
GROUP BY category
ORDER BY score DESC

is it possible to do that in single query?

UPDATE

I have rewrote my query as was suggested to use MySQL function MAX() however returned results are still wrong

here is example of new query

SELECT category, MAX(score) AS maxScore, title, score AS realScore
FROM places 
WHERE active = '1'
GROUP BY category
ORDER BY score DESC

分享到QQ

分享到微博

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

发布评论

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

鸢与 2024-12-02 23:08:46

SELECT category, MAX(score) max_score
FROM places
WHERE active = 1
GROUP BY category
ORDER BY max_score DESC, RAND()

SELECT category, MAX(score) max_score
FROM places
WHERE active = 1
GROUP BY category
ORDER BY max_score DESC, RAND()

回复收藏 0 原文

笑忘罢 2024-12-02 23:08:46

SELECT category
     , MAX(score) AS maxScore
FROM places
WHERE active = '1'
GROUP BY category

如果您还想要其他字段，除了 category 和（最大）score 之外，您可以使用子查询：

SELECT p.category
     , p.score
     , p.title
     , p.otherfield
     , ...
FROM
    places AS p
  JOIN
    ( SELECT category
           , MAX(score) AS maxScore
      FROM places
      WHERE active = '1'
      GROUP BY category
    ) AS grp
    ON  grp.category = p.category
    AND grp.maxscore = p.score
WHERE p.active = '1'
ORDER BY maxScore DESC

正如 @zekms 指出的，这将产生多行（带有相同类别）如果有多行具有相同类别和最大分数。

SELECT category
     , MAX(score) AS maxScore
FROM places
WHERE active = '1'
GROUP BY category

If you want other fields too, besides the category and (maximum) score, you can use a subquery:

SELECT p.category
     , p.score
     , p.title
     , p.otherfield
     , ...
FROM
    places AS p
  JOIN
    ( SELECT category
           , MAX(score) AS maxScore
      FROM places
      WHERE active = '1'
      GROUP BY category
    ) AS grp
    ON  grp.category = p.category
    AND grp.maxscore = p.score
WHERE p.active = '1'
ORDER BY maxScore DESC

As @zekms pointed, this will this will produce multiple rows (with same category) if there are several rows with the same category and max score.

回复收藏 0 原文