mysql数据表分析统计记录

表结构如下：

 

 

一、按人员分析统计

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