-- 禅道项目管理系统中bug进度统计汇总的sql(mysql)
select curdate() 汇总日期,p.name 产品
,concat(round(count(case when b.resolvedBy!='' and b.resolution!='tostory' then 1 end)/count(b.id)*100,2),'%') bug解决率
,count(b.id) as 总Bug
,count(case when b.resolvedBy!='' and b.resolution!='tostory' then 1 end) as 已解决Bug
,count(case when b.resolvedBy='' then 1 end) as 未解决Bug
,count(case when b.resolution='tostory' then 1 end) as bug转任务
,count(if(b.openedDate BETWEEN date_sub(curdate(), interval weekday(curdate()) + 14 day) AND date_sub(curdate(), interval weekday(curdate()) + 8 day),true,null)) 上上周新增Bug
,count(if(b.openedDate BETWEEN date_sub(curdate(), interval weekday(curdate()) + 7 day) AND date_sub(curdate(), interval weekday(curdate()) + 1 day),true,null)) 上周新增Bug
,count(case when b.openedDate>DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) then 1 end) as 本周新增Bug
,count(if(DATEDIFF(b.openedDate,NOW())=-2,true,null)) 前天新增Bug
,count(if(DATEDIFF(b.openedDate,NOW())=-1,true,null)) 昨天新增Bug
,count(if(DATEDIFF(b.openedDate,NOW())=0,true,null)) 今天新增Bug
,count(if(b.resolution!='tostory' and b.resolvedDate BETWEEN date_sub(curdate(), interval weekday(curdate()) + 14 day) AND date_sub(curdate(), interval weekday(curdate()) + 8 day),true,null)) 上上周解决Bug
,count(if(b.resolution!='tostory' and b.resolvedDate BETWEEN DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 7 DAY) AND DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY),true,null)) 上周解决Bug
,count(if(b.resolution!='tostory' and b.resolvedDate>DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY),true,null)) 本周解决Bug
,count(if(b.resolution!='tostory' and DATEDIFF(b.resolvedDate,NOW())=-2,true,null)) 前天解决Bug
,count(if(b.resolution!='tostory' and DATEDIFF(b.resolvedDate,NOW())=-1,true,null)) 昨天解决Bug
,count(if(b.resolution!='tostory' and DATEDIFF(b.resolvedDate,NOW())=0,true,null)) 今天解决Bug
from zt_bug b inner join zt_product p on p.id=b.product
where p.status='normal' and b.deleted='0'
GROUP BY p.id;
禅道项目管理系统中bug进度统计汇总的sql(mysql)
于 2023-06-12 23:27:22 首次发布