在SQL Server中仅显示计数的最大值

Question

我有以下问题：

SELECT   
B.ISBN, TITLE, COUNT(Rate) AS NumberOfRatings 
FROM 
bda.books AS B
LEFT JOIN 
bda.booksRatings as BR ON B.ISBN = BR.ISBN
GROUP BY 
B.ISBN, Title

我写了这个查询，我只想得到评分最高的书。我发现唯一的方法是添加：

HAVING 
COUNT(Rate) > 5

因为我得到的最大值是6。但是我不想把这个硬编码。

Answer 1

SELECT TOP (1) WITH TIES 
b.ISBN, 
b.title, 
RatingsCount = COUNT(*)
FROM bda.booksRatings AS br
INNER JOIN bda.books AS b
ON br.ISBN = b.ISBN
GROUP BY b.ISBN, b.title
ORDER BY RatingsCount DESC;

Answer 2

使用TOP，所有评分最高的书籍。

SELECT top(1) WITH TIES
B.ISBN, TITLE, COUNT(Rate) as NumberOfRatings 
FROM bda.books AS B
LEFT JOIN  bda.booksRatings as BR
on B.ISBN = BR.ISBN
GROUP BY B.ISBN, Title
ORDER BY COUNT(Rate) DESC;

Answer 3

您可以按评级数量订购，并将其限制为如下所示：

SELECT   
B.ISBN, TITLE, COUNT(Rate) as NumberOfRatings 
FROM bda.books AS B
LEFT JOIN 
bda.booksRatings as BR
on B.ISBN = BR.ISBN
GROUP BY B.ISBN, Title Order by NumberOfRatings limit to 1

或者您可以在NumberOfRatings上使用MAX函数，如下所示：

select B.ISBN, TITLE,Max(NumberOfRatings) from (SELECT   
B.ISBN, TITLE, COUNT(Rate) as NumberOfRatings 
FROM bda.books AS B
LEFT JOIN 
bda.booksRatings as BR
on B.ISBN = BR.ISBN
GROUP BY B.ISBN, Title)

两者都会起作用。