mysql - 计算并选择同一查询中列的最大值

Question

我有一个这样的数据集

+------+------------+------+
| user |    date    | code |
+------+------------+------+
|    1 | 2016-01-01 | AB   |
|    2 | 2016-02-03 | AS   |
|    3 | 2016-02-03 | AT   |
|    1 | 2016-01-27 | AB   |
|    2 | 2016-02-24 | AT   |
|    1 | 2016-01-23 | AS   |
|    2 | 2016-02-23 | AB   |
|    1 | 2016-02-16 | AS   |
|    1 | 2016-02-24 | AT   |
+------+------------+------+

我需要这样的东西

+------+---------------------------+
| user | max(count(distinct(code)) |
+------+---------------------------+
|    1 |                         2 |
|    2 |                         3 |
|    3 |                         1 |
+------+---------------------------+

所以我使用了这个查询

select t1.user, t1.month(date), count(distinct(`code`)) cod
from mytable t1
inner join 
(select user, max(count(distinct(`code`))) max_count
from mytable
group by user) t2
on t1.user = t2.user
and t1.cod = t2.max_count`

但我明白了:

Error code 1111. Invalid use of group function

Answer 1

尝试一下，使用按 user、month 划分的内部组和按 user 划分的外部组。

select t.user, max(t.cod) as max_cnt
from (
    select user, month(date) as month, count(distinct(`code`)) cod
    from mytable
    group by user, month
    ) t
group by t.user