mysql - 表中的最高值

Question

我有这个 SQL 查询来获取特定类别的书籍已被租借的次数。

SELECT Category.name, COUNT(*) AS "times rented"
FROM Category
INNER JOIN Book ON Book.category=Category.name
INNER JOIN Rent ON Rent.book_id=Book.id
GROUP BY Category.name
ORDER BY COUNT(*) DESC
LIMIT 1

现在，如果我想获得最受欢迎的书，这将非常有效。问题出在“LIMIT 1”上。我有多本书，其租借次数与上面查询的结果一样多。删除“LIMIT 1”只会返回所有租用的类别。

假设上面的查询返回这个

name | times rented
Adventure 153
Comics 153
Biography 102
Romance 80

如何同时返回“冒险”和“漫画”？

Answer 1

您可以使用 MAX()。

SELECT Category.name, COUNT(*) times_rented
FROM Category
INNER JOIN Book ON Book.category=Category.name
INNER JOIN Rent ON Rent.book_id=Book.id
INNER JOIN (select  MAX(COUNT(*)) max_count FROM Category GROUP BY Category.name) t ON t.id = Category.id AND Category.times_rented = t.max_count
GROUP BY Category.name