mysq 条件分组统计_分组统计每个人的bug权值

题：请计算出每个人处理的bug的权值，pri为1计3分，为2计2分，为3计1分，为4计0.5分。


#计算Bug权值,join方法一：
select  T1.openedBy as '姓名'  ,T1.A*3+T2.B*2 as 'Bug权值' from (
SELECT openedBy  , count(*) as 'A'
FROM `zt_bug` 
WHERE `openedDate` > '2016-01-01'   and pri = 1
group by `openedBy` 
        ) T1
left join 
(SELECT openedBy  , count(*) as 'B'
FROM `zt_bug` 
WHERE `openedDate` > '2016-01-01' and pri = 2
group by `openedBy`
)  T2
on T1.openedBy = T2.openedBy


#计算Bug权值,case方法2.1：
select resolvedby ,  
count(
case pri 
when 1 then 3 
when 2 then 2
when 3 then 1
when 4 then 0.5
else 0 end  
)
as 'bug权值'
from zt_bug
group by resolvedBy

#计算Bug权值,case方法2.2：
select resolvedby ,  
count(
case 
when pri = 1 then 3 
when pri = 2 then 2
when pri = 3 then 1
when pri = 4 then 0.5
else '' end  
)
as 'bug权值'
from zt_bug
group by resolvedBy

#计算Bug权值,if方法3：
select resolvedby ,
count(if(pri = 1,3,if(pri = 2,2,if(pri = 3,1,if(pri = 4,0.5,0)))))  
from zt_bug
group by resolvedBy