jira数据查询(按周统计线上bug,所有bug，bug占比) 做保存参考

港控/mmm° 2022-05-24 10:13 264阅读 0赞

转载自：https://blog.csdn.net/baidu\_21982563/article/details/40077191

SELECT IFNULL(c1,'total') AS 项目名称,

SUM(IF(c2='20',C3,0)) AS 第20周线上,

SUM(IF(c2='20',C4,0)) AS 第20周所有,

CONCAT(LEFT(IF(SUM(IF(c2='20',C3,0))=0,0,SUM(IF(c2='20',C3,0)) /SUM(IF(c2='20',C4,0)) )\*100 ,6),'%') AS 漏出占比,

SUM(IF(c2='21',C3,0)) AS 第21周线上,

SUM(IF(c2='21',C4,0)) AS 第21周所有,

CONCAT(LEFT(IF(SUM(IF(c2='21',C3,0))=0,0,SUM(IF(c2='21',C3,0)) /SUM(IF(c2='21',C4,0)) )\*100 ,6),'%') AS 漏出占比,

SUM(IF(c2='22',C3,0)) AS 第22周线上,

SUM(IF(c2='22',C4,0)) AS 第22周所有,

CONCAT(LEFT(IF(SUM(IF(c2='22',C3,0))=0,0,SUM(IF(c2='22',C3,0)) /SUM(IF(c2='22',C4,0)) )\*100 ,6),'%') AS 漏出占比,

SUM(IF(c2='23',C3,0)) AS 第23周线上,

SUM(IF(c2='23',C4,0)) AS 第23周所有,

CONCAT(LEFT(IF(SUM(IF(c2='23',C3,0))=0,0,SUM(IF(c2='23',C3,0)) /SUM(IF(c2='23',C4,0)) )\*100 ,6),'%') AS 漏出占比,

SUM(IF(c2='24',C3,0)) AS 第24周线上,

SUM(IF(c2='24',C4,0)) AS 第24周所有,

CONCAT(LEFT(IF(SUM(IF(c2='24',C3,0))=0,0,SUM(IF(c2='24',C3,0)) /SUM(IF(c2='24',C4,0)) )\*100 ,6),'%') AS 漏出占比

\#concat(left(if(SUM(IF(c2='2',C3,0))=0,0,SUM(IF(c2='2',C3,0)) /SUM(IF(c2='2',C4,0)) )\*100 ,6),'%') AS 漏出占比

\#SUM(C3) AS 漏出总数,

\#SUM(C4) AS 总数

FROM ( SELECT t1.c1,t1.c2,t1.c3,t2.c4 FROM

(SELECT b.pname AS c1,WEEK(a.created)+1 AS c2,COUNT(a.id) AS c3 FROM jiraissue a

LEFT JOIN project b ON a.project=b.id

LEFT JOIN issuetype c ON a.issuetype=c.id

WHERE a.created>='2014-1-1 00:00:00' AND a.issuetype=27 AND issuestatus NOT IN (10028,10029)

GROUP BY c1,c2) t1

LEFT JOIN

(SELECT b.pname AS c1,WEEK(a.created)+1 AS c2,COUNT(a.id) AS c4 FROM jiraissue a

LEFT JOIN project b ON a.project=b.id

LEFT JOIN issuetype c ON a.issuetype=c.id

WHERE a.created>='2014-1-1 00:00:00' AND a.issuetype IN(27,28) AND issuestatus NOT IN (10028,10029)

GROUP BY c1,c2)t2 ON t1.c1=t2.c1 AND t1.c2=t2.c2

GROUP BY t1.c1,t1.c2

)tx

GROUP BY C1 WITH ROLLUP ;

jira数据查询(按周统计线上bug,所有bug，bug占比) 做保存参考

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关使用python统计jira bug数据

相关 MySQL按日期分组统计历史所有数据

相关 MySQL基础——按月/周/日分组统计数据

相关 JIRA仪表盘统计项目bug等级数量教程

相关 MySql按周，按月，按日分组统计数据

相关 MySql按周，按月，按日分组统计数据

相关 jira数据查询(按周统计线上bug,所有bug，bug占比) 做保存参考

相关 jira数据查询(按周统计线上bug,所有bug，bug占比)

相关 Jira Bug类

相关 MySql按周，按月，按日分组统计数据