mysql - 计算 MYSQL 中的重复记录

Question

我需要一个查询来计算重复记录，

例如

table name -customer
===================

customer_id-col name
222
111
222
222
111
122

输出为

customer_id   count

222              3
111              2
222              3
222              3
111              2
122              1 

我尝试了这个查询

选择 customer_id,count( customer_id ) c来自客户按 customer_id 分组有 c >1

输出为

customer_id 计数

222              3
111              2
122              1 

这可能吗？提前致谢

谢谢拉惹

Answer 1

试试这个

SELECT T.customer_id,S.duplicate_count FROM
    (
    SELECT customer_id,count(customer_id) AS duplicate_count
    FROM yourtable group by customer_id
    HAVING (duplicate_count > 0)
    ) AS S Join yourtable On S.customer_id = T.customer_id

<强> FIDDLE DEMO

操作:

 customer_id       count    222              3    111              2    222              3    222              3    111              2    122              1