在MYSQL中使用COUNT(*)作为“COUNT”子句

Question

我试图在一列中查找、计数和报告所有重复条目的实例。任何知道如何做类似于我下面的尝试。

SELECT `Id`, COUNT(*) AS `COUNT`
FROM `testproductdata`
WHERE `COUNT` > 1
GROUP BY `Id`
ORDER BY `COUNT` DESC;

我得到了一个1054错误: where子句中的未知列COUNT。

Answer 1

使用HAVING over WHERE和GROUP BY

SELECT `Id`, COUNT(*) AS `COUNT`
FROM `testproductdata`
GROUP BY `Id`
HAVING  `COUNT` > 1
ORDER BY `COUNT` DESC;

我建议在count(*)上使用相关的名称表示为total_count而不是COUNT。

更改查询如下：

SELECT `Id`, COUNT(*) AS `total_count`
FROM `testproductdata`
GROUP BY `Id`
HAVING  `total_count` > 1
ORDER BY `total_count` DESC;

Answer 2

尝试将DISTINCT添加到查询中

SELECT * FROM (
SELECT DISTINCT `Id`, (SELECT COUNT(Id) FROM `testproductdata` WHERE `Id`=`Id`) `count`
FROM `testproductdata`
GROUP BY `Id`
HAVING `count` > 1
)
ORDER BY `count` DESC;

Answer 3

您应该更改where的位置，这应该是可行的：

SELECT Id, COUNT(1) AS count
FROM testproductdata
GROUP BY Id
HAVING COUNT(1) > 1
ORDER BY COUNT(1) DESC;