以下为二维表信息

//统计严重等级Bug

SELECT severity,count(severity) FROM `bf_bugview` where product_id=476 GROUP BY severity

//统计创建者Bug

SELECT created_by_name,count(created_by_name) as count FROM `bf_bugview` where product_id=476 GROUP BY created_by_name ORDER BY count ASC

//统计解决者Bug

SELECT resolved_by_name,count(resolved_by_name) as count FROM `bf_bugview` where product_id=476 GROUP BY resolved_by_name ORDER BY count ASC

//统计各个版本Bug

select bf_add.version version,count(*) from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id) WHERE b_info.product_id = 476 GROUP BY version

 

//统计每一轮的新引入Bug

select bf_add.version,count(*) from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id) WHERE b_info.product_id = 476 and b_info.newly_bug= '是' GROUP BY bf_add.version

 

//统计每一轮的漏测Bug

select bf_add.version,count(*) from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id) WHERE b_info.product_id = 476 and b_info.missing_bug= '是' GROUP BY bf_add.version

//统计每一轮激活Bug

select bf_add.version,count(*) from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id) WHERE b_info.product_id = 476 and b_info.missing_bug= '是' GROUP BY bf_add.version

//统计每一轮的用例外Bug

select bf_add.version,count(*) from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id) WHERE b_info.product_id = 476 and b_info.case_bug= '是' GROUP BY bf_add.version

//Bug 类型分布

select

//统计每一轮的解决方案为已经修复和设计如此的Bug

select bf_add.version,count(*) from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id) WHERE b_info.product_id = 476 and (b_info.solution = 'Fixed' or b_info.solution='By Design') GROUP BY bf_add.version

//统计每个模块下的Bug信息(一直到所有模块节点)

select module_name,count(module_name) FROM bf_bugview where product_id = 476 GROUP BY module_name

 

//统计某个产品下有哪些模块

select b_pro_m.full_path_name,count(b_pro_m.full_path_name) from bf_product_module b_pro_m join bf_bug_info as b_info on (b_info.productmodule_id = b_pro_m.id) where b_info.product_id=476 GROUP BY b_pro_m.full_path_name

 

以下为三维表信息

//统计模块下的Bug信息

 

 

 

 

select bf_add.version version,count(bf_add.version) as '总数',count(bf_add_new.version) as '新引入',count(bf_add_miss.version) as '漏测',count(bf_add_testcase.version) as '用例外Bug',count(bf_add_fixed.version) as '已经修复或者设计如此'

from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id)

left join bf_addonbug_476 as bf_add_new on (bf_add_new.bug_id=b_info.id and b_info.newly_bug= '是')

left join bf_addonbug_476 as bf_add_miss on (bf_add_miss.bug_id=b_info.id and b_info.missing_bug= '是')

left join bf_addonbug_476 as bf_add_testcase on (bf_add_testcase.bug_id=b_info.id and b_info.case_bug= '是')

left join bf_addonbug_476 as bf_add_fixed on (bf_add_fixed.bug_id=b_info.id and (b_info.solution = 'Fixed' or b_info.solution='By Design'))

WHERE b_info.product_id = 476 GROUP BY version

 

 

 

select bf_add.version version,count(bf_add.version) as 'count1',count(bf_add_new.version) as '新引入',count(bf_add_miss.version) as '漏测',count(bf_add_testcase.version) as '用例外Bug',count(bf_add_fixed.version) as '已经修复或者设计如此',count(bf_add_reopen.version) as '激活'

from bf_bug_info as b_info join bf_addonbug_476 as bf_add on (bf_add.bug_id=b_info.id)

left join bf_addonbug_476 as bf_add_new on (bf_add_new.bug_id=b_info.id and b_info.newly_bug= '是')

left join bf_addonbug_476 as bf_add_miss on (bf_add_miss.bug_id=b_info.id and b_info.missing_bug= '是')

left join bf_addonbug_476 as bf_add_testcase on (bf_add_testcase.bug_id=b_info.id and b_info.case_bug= '是')

left join bf_addonbug_476 as bf_add_fixed on (bf_add_fixed.bug_id=b_info.id and (b_info.solution = 'Fixed' or b_info.solution='By Design'))

left join bf_addonbug_476 as bf_add_reopen on (bf_add_reopen.bug_id=b_info.id and b_info.reopen_count != '0')

WHERE b_info.product_id = 476 GROUP BY version

 

 

全局统计

//统计每个产品提交的Bug单数目

select bp.name,count(bp.name) count from bf_bug_info bf JOIN bf_product bp ON (bp.id=bf.product_id) where bf.created_at BETWEEN 20160101 AND 20161001 GROUP BY bp.name ORDER BY count DESC

//关键字title匹配

select bf.id,bf.title,bp.name from bf_bug_info bf join bf_product bp on (bp.id=bf.product_id) WHERE bf.created_at BETWEEN 20160101 AND 20161009 and bf.title like "DHCP%"

 

//按每个人员提交的Bug单

select bt.realname,count(*) count from bf_bug_info bf JOIN bf_test_user bt ON (bf.created_by =bt.id) WHERE bf.created_at BETWEEN 20100101 AND 20161009 GROUP BY bt.username ORDER BY bt.username DESC

 

//安装