求一条统计用的sql解决方案

求一条统计用的sql
mh:工时 mhtype:工时类型（6种）

字段：userid mhtype mh
  001 1 5
  001 1 3
  001 2 4
  001 3 9
  002 5 5
  002 6 7
  002 3 4
  002 3 9

要求统计出每个人每一类工时的总合
结果要求如下格式
userid mhtype1 mhtype2 mhtype3 mhtype4 mhtype5 mhtype6 allmh
001 8 4 9 0 0 0 21
002 0 0 13 0 5 7 25

[解决办法]

SQL code

create table lk4(userid mediumint(3) unsigned zerofill not null,mhtype int not null,mh int not null);insert into lk4 values(001,1,5),(001,1,3),(001,2,4),(001,3,9),(002,5,5),(002,6,7),(002,3,4),(002,3,9);select userid,sum((case when mhtype=1 then mh else 0 end )) as 'mhtype1',sum((case when mhtype=2 then mh else 0 end )) as 'mhtype2',sum((case when mhtype=3 then mh else 0 end )) as 'mhtype3',sum((case when mhtype=4 then mh else 0 end )) as 'mhtype4',sum((case when mhtype=5 then mh else 0 end )) as 'mhtype5',sum((case when mhtype=6 then mh else 0 end )) as 'mhtype6',sum(mh) as 'allmh'from lk4 group by userid;