如何计算MySQL中非唯一的值组合？

I have a table with some legacy data that I suspect may be a little messed up. It is a many-to-many join table.

我有一张包含一些遗留数据的表格,我怀疑这些数据可能有些混乱。它是一个多对多的连接表。

LIST_MEMBERSHIPS
----------------
list_id
address_id

I'd like to run a query that will count the occurrences of each list_id-address_id pair and show the occurrence count for each from highest to lowest number of occurrences.

我想运行一个查询来计算每个list_id-address_id对的出现次数,并显示每个出现次数从最高到最低的出现次数。

I know it's got to involve COUNT() and GROUP BY, right?

我知道它必须涉及COUNT()和GROUP BY,对吗?

2 个解决方案

#1

select list_id, address_id, count(*) as count
from LIST_MEMBERSHIPS
group by 1, 2
order by 3 desc

You may find it useful to add

您可能会发现添加它很有用

having count > 1

#2

select count(*), list_id, address_id
from list_membership
group by list_id, address_id
order by count(*) desc