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

posted on 2015-09-08 15:55 林中侠客 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/luojianqun/p/4792007.html