MySQL查询仅返回带有计数的重复条目

I have a legacy MySQL table called lnk_lists_addresses with columns list_id and address_id. I'd like to write a query that reports all the cases where the same list_id-address_id combination appears more than once in the table with a count.

我有一个名为lnk_lists_addresses的遗留MySQL表,列有list_id和address_id。我想编写一个查询报告所有情况,其中相同的list_id-address_id组合在带有计数的表中出现多次。

I tried this...

我试过这个......

SELECT count(*), list_id, address_id
FROM lnk_lists_addresses
GROUP BY list_id, address_id
ORDER BY count(*) DESC
LIMIT 20

It works, sort of, because there are fewer than 20 duplicates. But how would I return only the counts greater than 1?

它的工作原理,因为重复次数少于20次。但是,我如何只返回大于1的计数呢?

I tried adding "WHERE count(*) > 1" before and after GROUP BY but got errors saying the statement was invalid.

我尝试在GROUP BY之前和之后添加“WHERE count(*)> 1”但是错误地说该语句无效。

3 个解决方案

#1

SELECT count(*), list_id, address_id
FROM lnk_lists_addresses
GROUP BY list_id, address_id
HAVING count(*)>1
ORDER BY count(*) DESC

To combine mine and Todd.Run's answers for a more "complete" answer. You want to use the HAVING clause:

将我和Todd.Run的答案结合起来,以获得更“完整”的答案。您想使用HAVING子句:

http://dev.mysql.com/doc/refman/5.1/en/select.html

#2

You want to use a "HAVING" clause. Its use is explained in the MySQL manual.

您想使用“HAVING”子句。它的用法在MySQL手册中有解释。

http://dev.mysql.com/doc/refman/5.1/en/select.html

#3

SELECT count(*) AS total, list_id, address_id
FROM lnk_lists_addresses
WHERE total > 1
GROUP BY list_id, address_id
ORDER BY total DESC
LIMIT 20

If you name the COUNT() field, you can use it later in the statement.

如果您命名COUNT()字段,则可以稍后在语句中使用它。

EDIT: forgot about HAVING (>_<)

编辑:忘记了HAVING(> _ <)