该SQL查询用于计算任务的状态统计，包括逾期(overdue)、即将逾期(goingOverdue)、准时完成(periodTotalOnTime)、总任务(total)、已完成(completionTotal)和未完成(uncompletion)。根据status和complete_time判断任务状态，并按created_time进行日期分组，支持日、周、月和年的粒度查询。
select count(
case when (case when status = 1 and complete_time =null then NOW() else complete_time end ) > estimated_finish_time then id else null end
) as overdue,
case
when #{queryDO.period} =0 then DATE_FORMAT(created_time,"%m-%d")
when #{queryDO.period}=1 then WEEK(created_time)
when #{queryDO.period}=2 then DATE_FORMAT(created_time,"%Y-%m")
when #{queryDO.period}=3 then DATE_FORMAT(created_time,"%Y")
end
as date,
count(
case when date_add((case when status = 1 and complete_time = null then NOW() else complete_time end),INTERVAL 1 DAY)
> estimated_finish_time then id else null end
) as goingOverdue,
count( case when status=2 and estimated_finish_time >= complete_time then id else null end ) as periodTotalOnTime,
count(id) as total,
count(case when status=2 then id else null end) as completionTotal,
count(case when status!=2 then id else null end) as uncompletioncase 一定要 使用end 结束 多个when也不需要用逗号隔开
扫一扫
2074
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
