表结构如下:
一、按人员分析统计
select issue.assignee, count(*) from issue GROUP BY assignee;
二、按时间统计, 统计今天新增和更新的问题
select created , COUNT(*)as createsum from issue GROUP BY created ;
select updated , COUNT(*) as updatesum from issue GROUP BY updated ;
把上面两个结果, 合并成三列, 时间为一列, 其余为两列
实验一下sql语句:
左连接,右连接, 合并 去重
select * from (
(select created , COUNT(*) as createsum from issue GROUP BY created ) a RIGHT JOIN
(select updated , COUNT(*) as updatesum from issue GROUP BY updated ) b on a.created = b.updated)
union
(
select * from (
(select created , COUNT(*) as createsum from issue GROUP BY created ) a left JOIN
(select updated , COUNT(*) as updatesum from issue GROUP BY updated ) b on a.created = b.updated)) ORDER BY created, updated
结果为:
是四列, 不太符合要求
继续改进:
select date, sum(create_sum), sum(update_sum) from
(
select created as date,count(created) as create_sum,null as update_sum from issue group by created
UNION
select updated as date,null as create_sum ,count(updated) as update_sum from issue group by updated
) c GROUP BY date ORDER BY date
OK!
三、 统计各产品各级别bug数量
select product_name , priority , count(*) from issue where customfield_10201= '版本' GROUP BY product_name, priority