如何计算SQL中派生表的出现次数

Question

我有一个非常简单的表：

CREATE TABLE MyTable 
( 
Id INT(6) PRIMARY KEY,
Name VARCHAR(200) /* NOT UNIQUE */
); 

如果我想要最频繁的名字和相应的计数，我既不能做这个

SELECT Name, total
FROM table2
WHERE total = (SELECT MAX(total) FROM (SELECT Name, COUNT(*) AS total
FROM MyTable GROUP BY Name) table2);

也没有这个

SELECT Name, total
FROM (SELECT Name, COUNT(*) AS total FROM MyTable GROUP BY Name) table1
WHERE total = (SELECT MAX(total) FROM table1);

此外，(假设最大计数为4(在第二个命题中，如果我用替换第三行

WHERE total = 4;

它是有效的
为什么会这样？

非常感谢

Answer 1

您可以尝试以下操作：

WITH stats as
(
SELECT Name
,COUNT(id) as count_ids
FROM MyTable
GROUP BY Name
)
SELECT Name
,count_ids
FROM
(
SELECT Name
,count_ids
,RANK() OVER(ORDER BY count_ids DESC) as rank_ -- this ranks all names
FROM stats
) s
WHERE rank_ = 1 -- the most popular ```
This should work in TSQL.

Answer 2

您的查询无法执行，因为"合计"；在您的表中没有列。仅仅将其包含在子查询中是不够的，您还必须确保子查询将被执行，产生所需的结果，然后才能使用它。

你还应该考虑使用一个窗口函数，就像Dimi的回答中提出的那样。这样一个函数的优点是它可以更容易地阅读。但您需要小心，因为这些函数通常因数据库类型而异。

如果你想使用子查询，你可以这样做：

SELECT name, COUNT(name) AS total FROM myTable
GROUP BY name
HAVING COUNT(name) =
(SELECT MAX(sub.total) AS highestCount FROM
(SELECT Name, COUNT(*) AS total
FROM MyTable GROUP BY Name) sub);

我创建了一个fiddle示例，显示这里提到的两个查询将产生相同且正确的结果：db<gt；小提琴