假如我有下面的表：
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
