直接代码
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; -- 根据单子负责人的名字分组