mysql查看列状态_mysql统计一个字段的多种状态

假如我有下面的表：

ID    Item           status            updatetime    author

1    a        1        2014-01-02     Mike

2    a        1        2014-02-01     Jack

3    b        2        2014-01-10     John

4    b        1        2014-10-10     Ben

5    b        2        2014-01-11     Sam

6    c        3        2014-01-02     Aron

7    c        1        2014-11-01     Aron

8    c        1        2014-10-20     Max

9    d        3        2014-10-20     Mike

想统计对于每一个item，每一种status的个数为多少，同时记录器最新的时间和作者，结果如下：

Item    status_1    status_2    status_3    Latestupdate    author

a        2             0          0         2014-02-01        Jack

b        1             2          0         2014-10-10        Ben

c        2             0          1         2014-11-01        Aron

d        0             0          1         2014-10-20        Mike

select item,

sum(case when status = 1 then 1 else 0 end) as status_1,

sum(case when status = 2 then 1 else 0 end) as status_2,

sum(case when status = 2 then 1 else 0 end) as status_3

max(update time) as Latestupdate,

author

from table

group by item, status_1, status_2, status_3