mysql - 获取mysql中重复值的多个最大计数

Question

如何从表中获取重复值最大计数的输出，该表包含与列相对应的重复值，这样就有多个不同的不同值具有最大计数。

考虑下面的表格数据:

+---------+------------+-------------+--------------+
| Bill_No | Bill_Date  | Customer_ID | Total_Amount | 
+---------+------------+-------------+--------------+
|     101 | 2012-04-10 | C001        |           64 |
|     102 | 2012-04-10 | C002        |            8 |
|     103 | 2012-04-11 | C002        |          140 |
|     104 | 2012-04-13 | C001        |           29 |
|     105 | 2012-04-12 | C003        |          125 |
|     106 | 2012-04-16 | C004        |          258 |
+---------+------------+-------------+--------------+    

我们在这里看到 C001 和 C002 的最大 count(customer_id) 是相同的。我想获得这两个值。

最终输出应如下所示:

customer_id     |    count(customer_id)  //max value
----------------+-----------------------
C001            |    2                  
C002            |    2                  
----------------+-----------------------

Answer 1

所以，经过一些猜测，会

select
    distinct g.customer_id,
    g.cnt
from
    (
    select
        distinct customer_id,
        count(customer_id) cnt
    from
        table
    group by 
        customer_id
    ) g
inner join
    (
    select
        max(s.cnt) max_cnt
    from
        (
        select
            distinct customer_id,
            count(customer_id) cnt
        from
            table
        group by
            customer_id
        ) s
    ) m
on
    m.max_cnt = g.cnt

有技巧吗？