求一条统计用的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;