sql根据人员分组计算单子数量 根据状态计算单子数量 平均耗时

直接代码

 

select 
COUNT(*) AS receivedNumber, -- 计算合计数
COUNT(*) AS processedNumber,  
SUM(CASE WHEN t1.status = '5' THEN 1 ELSE 0 END) AS completeNumber,  -- 计算t1表字段status等于5的单子的数量 
SUM(CASE WHEN t1.result = '6' THEN 1 ELSE 0 END) AS activationNumber,
SUM(CASE WHEN t1.result <> '6' THEN 1 ELSE 0 END) AS notNumber,  -- 计算t1表字段result不等于5的单子的数量 
t2.realname AS engineerName ,
ifnull( ROUND( avg( TIMESTAMPDIFF( MINUTE, t3.create_time, t1.worker_over_time ) ) / 60, 2 ), 0 ) AS averageTime 
-- TIMESTAMPDIFF(MINUTE, t2.create_time, t.worker_over_time)：计算两个时间之间的分钟差  /60 是以小时为单位
-- AVG()：计算平均值。
-- ROUND(..., 2)：将结果四舍五入到小数点后两位。
-- IFNULL(..., 0)：如果计算结果为空，则返回 0。
from wk_form_info t1 left join sys_user t2  on t1.worker_id = t2.id 
left join wk_form_node t3  on t3.form_id = t1.id and node_type ='2'
GROUP BY t2.realname;  -- 根据单子负责人的名字分组