今天发现了之前的一段sql，查询起来特别慢，于是决定优化一番，最终调整效果显著(4.6s-0.25s)，现在来看看吧。
原始sql，一共599条数据，查询用时4.6s
select xx.*
from (
select m.*,
(case when m.plan_type = 'academic' then
(
select count(1) from nurse_edu_academic_plan_service_member apsm where apsm.plan_id = m.id and apsm.delete_bit = 0
)
when m.plan_type = 'operate' then
(
select count(1) from nurse_edu_operate_plan_service_member opsm where opsm.plan_id = m.id and opsm.delete_bit = 0
)
when m.plan_type = 'practice' then
(
select count(1) from nurse_edu_practice_plan_service_member ppsm where ppsm.plan_id = m.id and ppsm.delete_bit = 0
)
when m.plan_type = 'training' then
(
select count(1) from nurse_edu_training_plan_service_member tpsm where tpsm.plan_id = m.id and tpsm.delete_bit = 0
)
when m.plan_type = 'course' then
(
SELECT count(1) FROM `nurse_edu_course_plan_service_member` AS cpsm where cpsm.plan_id = m.id and cpsm.delete_bit = 0
)
when m.plan_type = 'courseware' then
(
SELECT count(1) FROM `nurse_edu_courseware_plan_service_member` AS cpsm where cpsm.plan_id = m.id and cpsm.delete_bit = 0
)
else 0 end
) as personCount,
(case when m.plan_type = 'academic' then
(
select count(1) from nurse_edu_academic_plan_service_member apsm where apsm.plan_id = m.id and (apsm.exam_status = 'completed'
) and apsm.delete_bit = 0
)
when m.plan_type = 'operate' then
(
select count(1) from nurse_edu_operate_plan_service_member opsm where opsm.plan_id = m.id and (opsm.exam_status = 'completed' or opsm.exam_status = 'doing'
) and opsm.delete_bit = 0
)
when m.plan_type = 'practice' then
(
ifnull(n.practiceActualNum,0)
)
when m.plan_type = 'training' then
(
case when m.planCompleteCondition = 'sign_in' then
(
select count(1) from nurse_edu_training_plan_service_member tpsm where tpsm.plan_id = m.id and IFNULL(tpsm.sign_in, 0) = 1 and tpsm.delete_bit = 0
)
when m.planCompleteCondition = 'sign_out' then
(
select count(1) from nurse_edu_training_plan_service_member tpsm where tpsm.plan_id = m.id and IFNULL(tpsm.sign_out, 0) = 1 and tpsm.delete_bit = 0
)
when m.planCompleteCondition = 'exam' then
(
select count(1) from nurse_edu_training_plan_service_member tpsm where tpsm.plan_id = m.id and
(
tpsm.exam_actual_end_time is not null
)
and tpsm.delete_bit = 0
)
when m.planCompleteCondition = 'questionnaire' then
(
select count(1) from nurse_edu_training_plan_implement_questionnaire tpiq where
tpiq.plan_id = m.id
and tpiq.service_member_id in ( select tpsm.id from nurse_edu_training_plan_service_member tpsm where tpsm.plan_id = m.id )
)
when (m.planCompleteCondition = '' or m.planCompleteCondition is null) then
(
select count(1) from nurse_edu_training_plan_service_member tpsm where tpsm.plan_id = m.id and tpsm.delete_bit = 0
)
else '' end
)
when m.plan_type = 'course' then
(
SELECT count(1) FROM `nurse_edu_course_plan_service_member` AS cpsm
LEFT JOIN (SELECT plan_id,COUNT(1) as sumNum FROM nurse_edu_course_video WHERE delete_bit=0 GROUP BY plan_id) AS
cv ON cv.plan_id=cpsm.plan_id AND cpsm.delete_bit=0
LEFT JOIN (
SELECT service_member_id,COUNT(course_video_id) AS cnt FROM (SELECT service_member_id,course_video_id FROM `nurse_edu_course_plan_implement_video` WHERE
CONCAT('0-',video_total_duration)=video_play_recording
GROUP BY service_member_id,course_video_id) AS f GROUP BY service_member_id
) AS t ON t.service_member_id=cpsm.id
WHERE cpsm.plan_id= m.id
and (case when m.planCompleteCondition = 'exam' then
(case when (
cpsm.exam_actual_end_time IS NOT NULL
) THEN 1 ELSE 0 END)
when m.planCompleteCondition = 'played' then
(ROUND(t.cnt/cv.sumNum,2))
else 0 end)=1
)
when m.plan_type = 'courseware' then
(
SELECT count(1)
FROM `nurse_edu_courseware_plan_service_member` AS cpsm
LEFT JOIN nurse_edu_courseware_plan_implement_view AS iv ON iv.service_member_id=cpsm.id AND cpsm.delete_bit=0
WHERE cpsm.plan_id=m.id
and (
case when m.planCompleteCondition = 'exam' then
(case when (
cpsm.exam_actual_end_time IS NOT NULL
) THEN 1 ELSE 0 END)
when m.planCompleteCondition = 'viewed' then
(case when IFNULL(iv.view_total_duration,0)>0 then 1 else 0 end)
else 0 end)=1
)
else 0 end
) as actualCompletePepoleCount,
(
case when (m.plan_type = 'training' and m.plan_status = 'undone' and CURRENT_TIMESTAMP > m.plan_end_time) then
(
'completed'
)
when (m.plan_status = 'undone' and CURRENT_TIMESTAMP < m.plan_start_time) then
(
'nostart'
)
when (m.plan_status = 'undone' and m.plan_start_time < CURRENT_TIMESTAMP and CURRENT_TIMESTAMP < m.plan_end_time) then
(
'doing'
)
else m.plan_status end
) as queryPlanStatus
from
(SELECT
CONCAT(IFNULL(aps.plan_complete_pepole_count, ''), IFNULL(ops.plan_complete_pepole_count, ''),
IFNULL(pps.plan_complete_people_count, ''), IFNULL(cps.plan_complete_people_count, ''),
IFNULL(tps.plan_complete_people_count, ''), IFNULL(cwps.plan_complete_people_count, '')) AS
planCompletePepoleCount,
CONCAT(IFNULL(pps.target_complete_percentage, ''), IFNULL(cps.plan_complete_condition,
''),IFNULL(tps.plan_complete_condition, ''), IFNULL(cwps.plan_complete_condition, '')) AS planCompleteCondition,
IFNULL(tps.out_lecturer_user_names, '') AS outLecturerUserNames,
IFNULL(tps.out_lecturer_user_phones, '') AS outLecturerUserPhones,
IFNULL(tps.already_send_sms_phones, '') AS alreadySendSmsPhones,
a.*,
item.plan_add_member_range as planAddMemberRange
FROM `nurse_edu_item_plan` AS a
left join nurse_edu_item as item on item.id = a.item_id
LEFT JOIN nurse_edu_academic_plan_service AS aps ON a.id = aps.plan_id AND aps.delete_bit = 0
LEFT JOIN nurse_edu_operate_plan_service as ops ON a.id = ops.plan_id AND ops.delete_bit = 0
LEFT JOIN nurse_edu_practice_plan_service AS pps ON a.id = pps.plan_id AND pps.delete_bit = 0
LEFT JOIN nurse_edu_course_plan_service AS cps ON a.id = cps.plan_id AND cps.delete_bit = 0
LEFT JOIN nurse_edu_training_plan_service as tps ON a.id = tps.plan_id AND tps.delete_bit = 0
LEFT JOIN nurse_edu_courseware_plan_service AS cwps ON a.id = cwps.plan_id ANd cwps.delete_bit = 0
WHERE
IFNULL(a.make_up_belong_plan_id,0) = 0
AND a.delete_bit = 0
) m left join
( select count(1) as practiceActualNum, pp.planId as planId
from (
SELECT
(case when IFNULL(ROUND(100 * COUNT(cs.id) / p.all_subject_count, 2), 0) >= p.target_complete_percentage then 'done' else 'undone' end) AS completeStatus,
ip.id as planId
FROM `nurse_edu_practice_plan_service` as p
LEFT JOIN nurse_edu_item_plan AS ip ON p.plan_id = ip.id
LEFT JOIN nurse_edu_practice_plan_service_member AS ppsm ON p.id = ppsm.service_id AND p.delete_bit = '0' AND ppsm.delete_bit = '0'
LEFT JOIN nurse_edu_practice_complete_subject as cs ON cs.service_member_id = ppsm.id
GROUP BY ppsm.member_user_id,planId
) pp where pp.completeStatus='done' GROUP BY planId
) n on m.id = n.planId
) xx
where 1=1
ORDER BY xx.create_time desc,xx.last_update_time desc第一次优化，整体查询耗时0.9s
SQL中使用了较多的子查询，子查询会影响查询速度
去除子查询部分，耗时0.88s，查询速度有明显变化
原sql中，使用case when做大量条件判断+子查询，处理不同情况下的数据展示。可以尝试使用left join替代子查询，那就需要将每一种情况写成一个子表，再根据条件关联主表显示数据
统计总人数的子表查询
实际完成人数子表查询
与主表的关联条件
第一次优化后sql
select xx.*
from (
select m.*,
ff.personCount,
mm.actualCompletePepoleCount,
(
case when (m.plan_type = 'training' and m.plan_status = 'undone' and CURRENT_TIMESTAMP > m.plan_end_time) then
(
'completed'
)
when (m.plan_status = 'undone' and CURRENT_TIMESTAMP < m.plan_start_time) then
(
'nostart'
)
when (m.plan_status = 'undone' and m.plan_start_time < CURRENT_TIMESTAMP and CURRENT_TIMESTAMP < m.plan_end_time) then
(
'doing'
)
else m.plan_status end
) as queryPlanStatus
from
(
SELECT
CONCAT(IFNULL(aps.plan_complete_pepole_count, ''), IFNULL(ops.plan_complete_pepole_count, ''),
IFNULL(pps.plan_complete_people_count, ''), IFNULL(cps.plan_complete_people_count, ''),
IFNULL(tps.plan_complete_people_count, ''), IFNULL(cwps.plan_complete_people_count, '')) AS
planCompletePepoleCount,
CONCAT(IFNULL(pps.target_complete_percentage, ''), IFNULL(cps.plan_complete_condition,
''),IFNULL(tps.plan_complete_condition, ''), IFNULL(cwps.plan_complete_condition, '')) AS planCompleteCondition,
IFNULL(tps.out_lecturer_user_names, '') AS outLecturerUserNames,
IFNULL(tps.out_lecturer_user_phones, '') AS outLecturerUserPhones,
IFNULL(tps.already_send_sms_phones, '') AS alreadySendSmsPhones,
a.*,
item.plan_add_member_range as planAddMemberRange
FROM `nurse_edu_item_plan` AS a
left join nurse_edu_item as item on item.id = a.item_id
LEFT JOIN nurse_edu_academic_plan_service AS aps ON a.id = aps.plan_id AND aps.delete_bit = 0
LEFT JOIN nurse_edu_operate_plan_service as ops ON a.id = ops.plan_id AND ops.delete_bit = 0
LEFT JOIN nurse_edu_practice_plan_service AS pps ON a.id = pps.plan_id AND pps.delete_bit = 0
LEFT JOIN nurse_edu_course_plan_service AS cps ON a.id = cps.plan_id AND cps.delete_bit = 0
LEFT JOIN nurse_edu_training_plan_service as tps ON a.id = tps.plan_id AND tps.delete_bit = 0
LEFT JOIN nurse_edu_courseware_plan_service AS cwps ON a.id = cwps.plan_id ANd cwps.delete_bit = 0
WHERE
IFNULL(a.make_up_belong_plan_id,0) = 0
AND a.delete_bit = 0
) m left join
(
select m.plan_id,count(1) as personCount from nurse_edu_academic_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_operate_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_practice_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_training_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_course_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_courseware_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id
) ff on ff.plan_id = m.id
left join (
select m.plan_id,count(1) as actualCompletePepoleCount,'academic' as actualType from nurse_edu_academic_plan_service_member m where m.exam_status = 'completed' and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'operate' as actualType from nurse_edu_operate_plan_service_member m where (m.exam_status = 'completed' or m.exam_status = 'doing') and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_sign_in' as actualType from nurse_edu_training_plan_service_member m where IFNULL(m.sign_in, 0) = 1 and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_sign_out' as actualType from nurse_edu_training_plan_service_member m where IFNULL(m.sign_out, 0) = 1 and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_exam' as actualType from nurse_edu_training_plan_service_member m where m.exam_actual_end_time is not null and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_questionnaire' as actualType from nurse_edu_training_plan_implement_questionnaire m GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_other' as actualType from nurse_edu_training_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'course_exam' FROM `nurse_edu_course_plan_service_member` AS cpsm
WHERE cpsm.exam_actual_end_time IS NOT NULL and cpsm.delete_bit=0
GROUP BY cpsm.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'course_played' FROM `nurse_edu_course_plan_service_member` AS cpsm
LEFT JOIN (SELECT plan_id,COUNT(1) as sumNum FROM nurse_edu_course_video WHERE delete_bit=0 GROUP BY plan_id) AS
cv ON cv.plan_id=cpsm.plan_id AND cpsm.delete_bit=0
LEFT JOIN (
SELECT service_member_id,COUNT(course_video_id) AS cnt FROM (SELECT service_member_id,course_video_id FROM `nurse_edu_course_plan_implement_video` WHERE
CONCAT('0-',video_total_duration)=video_play_recording
GROUP BY service_member_id,course_video_id) AS f GROUP BY service_member_id
) AS t ON t.service_member_id=cpsm.id
WHERE cpsm.exam_actual_end_time IS NOT NULL and ROUND(t.cnt/cv.sumNum,2) = 1
GROUP BY cpsm.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'courseware_exam' as actualType
FROM `nurse_edu_courseware_plan_service_member` AS cpsm
LEFT JOIN nurse_edu_courseware_plan_implement_view AS iv ON iv.service_member_id=cpsm.id AND cpsm.delete_bit=0
WHERE cpsm.exam_actual_end_time IS NOT NULL
GROUP BY cpsm.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'courseware_viewed' as actualType
FROM `nurse_edu_courseware_plan_service_member` AS cpsm
LEFT JOIN nurse_edu_courseware_plan_implement_view AS iv ON iv.service_member_id=cpsm.id AND cpsm.delete_bit=0
WHERE IFNULL(iv.view_total_duration,0)>0
GROUP BY cpsm.plan_id
union all
select pp.planId as planId, count(1) as actualCompletePepoleCount,'practice' as actualType
from (
SELECT
(case when IFNULL(ROUND(100 * COUNT(cs.id) / p.all_subject_count, 2), 0) >= p.target_complete_percentage then 'done' else 'undone' end) AS completeStatus,
ip.id as planId
FROM `nurse_edu_practice_plan_service` as p
LEFT JOIN nurse_edu_item_plan AS ip ON p.plan_id = ip.id
LEFT JOIN nurse_edu_practice_plan_service_member AS ppsm ON p.id = ppsm.service_id AND p.delete_bit = '0' AND ppsm.delete_bit = '0'
LEFT JOIN nurse_edu_practice_complete_subject as cs ON cs.service_member_id = ppsm.id
GROUP BY ppsm.member_user_id,planId
) pp where pp.completeStatus='done' GROUP BY planId
) mm on m.id = mm.plan_id and
(case when m.plan_type = 'academic' and mm.actualType = 'academic' then 1
when m.plan_type = 'operate' and mm.actualType = 'operate' then 1
when m.plan_type = 'practice' and mm.actualType = 'practice' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'sign_in' and mm.actualType = 'trainging_sign_in' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'sign_out' and mm.actualType = 'training_sign_out' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'exam' and mm.actualType = 'training_exam' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'questionnaire' and mm.actualType = 'training_questionnaire' then 1
when m.plan_type ='training' and (m.planCompleteCondition = '' or m.planCompleteCondition is null) and mm.actualType = 'training_other' then 1
when m.plan_type = 'course' and m.planCompleteCondition = 'exam' and mm.actualType = 'courseware_exam' then 1
when m.plan_type = 'course' and m.planCompleteCondition = 'viewed' and mm.actualType = 'courseware_viewed' then 1
when m.plan_type = 'courseware' and m.planCompleteCondition = 'exam' and mm.actualType = 'course_exam' then 1
when m.plan_type = 'courseware' and m.planCompleteCondition = 'played' and mm.actualType = 'course_played' then 1
else 0 end) = 1
) xx
where 1=1
ORDER BY xx.create_time desc,xx.last_update_time desc第二次优化，整体耗时0.25s
经查，主体部分sql执行，耗时0.5s左右，增加索引执行无明显变化
尝试将相同业务数据表放在一起查询必要字段，再与主业务关联，耗时0.07s
优化后整体sql
select xx.*
from (
select m.*,
ff.personCount,
mm.actualCompletePepoleCount,
(
case when (m.plan_type = 'training' and m.plan_status = 'undone' and CURRENT_TIMESTAMP > m.plan_end_time) then
(
'completed'
)
when (m.plan_status = 'undone' and CURRENT_TIMESTAMP < m.plan_start_time) then
(
'nostart'
)
when (m.plan_status = 'undone' and m.plan_start_time < CURRENT_TIMESTAMP and CURRENT_TIMESTAMP < m.plan_end_time) then
(
'doing'
)
else m.plan_status end
) as queryPlanStatus
from
(
SELECT
a.*,
bb.planCompletePepoleCount,
bb.planCompleteCondition,
bb.outLecturerUserNames,
bb.outLecturerUserPhones,
bb.alreadySendSmsPhones,
item.plan_add_member_range as planAddMemberRange
FROM `nurse_edu_item_plan` AS a
left join nurse_edu_item as item on item.id = a.item_id
left join
(
select aps.plan_complete_pepole_count as planCompletePepoleCount,'' as planCompleteCondition,'' AS outLecturerUserNames,'' AS outLecturerUserPhones,'' AS alreadySendSmsPhones,aps.plan_id as planId from nurse_edu_academic_plan_service AS aps where aps.delete_bit = 0
union all
select ops.plan_complete_pepole_count as planCompletePepoleCount,'' as planCompleteCondition,'' AS outLecturerUserNames,'' AS outLecturerUserPhones,'' AS alreadySendSmsPhones,ops.plan_id as planId from nurse_edu_operate_plan_service AS ops where ops.delete_bit = 0
union all
select pps.plan_complete_people_count as planCompletePepoleCount,pps.target_complete_percentage as planCompleteCondition,'' AS outLecturerUserNames,'' AS outLecturerUserPhones,'' AS alreadySendSmsPhones,pps.plan_id as planId from nurse_edu_practice_plan_service AS pps where pps.delete_bit = 0
union all
select cps.plan_complete_people_count as planCompletePepoleCount,cps.plan_complete_condition as planCompleteCondition,'' AS outLecturerUserNames,'' AS outLecturerUserPhones,'' AS alreadySendSmsPhones,cps.plan_id as planId from nurse_edu_course_plan_service AS cps where cps.delete_bit = 0
union all
select tps.plan_complete_people_count as planCompletePepoleCount,tps.plan_complete_condition as planCompleteCondition,IFNULL(tps.out_lecturer_user_names, '') AS outLecturerUserNames,IFNULL(tps.out_lecturer_user_phones, '') AS outLecturerUserPhones,IFNULL(tps.already_send_sms_phones, '') AS alreadySendSmsPhones,tps.plan_id as planId
from nurse_edu_training_plan_service AS tps where tps.delete_bit = 0
union all
select cwps.plan_complete_people_count as planCompletePepoleCount,cwps.plan_complete_condition as planCompleteCondition,'' AS outLecturerUserNames,'' AS outLecturerUserPhones,'' AS alreadySendSmsPhones,cwps.plan_id as planId from nurse_edu_courseware_plan_service AS cwps where cwps.delete_bit = 0
) bb on a.id = bb.planId
WHERE
IFNULL(a.make_up_belong_plan_id,0) = 0
AND a.delete_bit = 0
) m left join
(
select m.plan_id,count(1) as personCount from nurse_edu_academic_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_operate_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_practice_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_training_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_course_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id union all
select m.plan_id,count(1) as personCount from nurse_edu_courseware_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id) ff on ff.plan_id = m.id
left join (
select m.plan_id,count(1) as actualCompletePepoleCount,'academic' as actualType from nurse_edu_academic_plan_service_member m where m.exam_status = 'completed' and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'operate' as actualType from nurse_edu_operate_plan_service_member m where (m.exam_status = 'completed' or m.exam_status = 'doing') and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_sign_in' as actualType from nurse_edu_training_plan_service_member m where IFNULL(m.sign_in, 0) = 1 and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_sign_out' as actualType from nurse_edu_training_plan_service_member m where IFNULL(m.sign_out, 0) = 1 and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_exam' as actualType from nurse_edu_training_plan_service_member m where m.exam_actual_end_time is not null and m.delete_bit = 0 GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_questionnaire' as actualType from nurse_edu_training_plan_implement_questionnaire m GROUP BY m.plan_id
union all
select m.plan_id,count(1) as actualCompletePepoleCount,'training_other' as actualType from nurse_edu_training_plan_service_member m where m.delete_bit = 0 GROUP BY m.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'course_exam' FROM `nurse_edu_course_plan_service_member` AS cpsm
WHERE cpsm.exam_actual_end_time IS NOT NULL and cpsm.delete_bit=0
GROUP BY cpsm.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'course_played' FROM `nurse_edu_course_plan_service_member` AS cpsm
LEFT JOIN (SELECT plan_id,COUNT(1) as sumNum FROM nurse_edu_course_video WHERE delete_bit=0 GROUP BY plan_id) AS
cv ON cv.plan_id=cpsm.plan_id AND cpsm.delete_bit=0
LEFT JOIN (
SELECT service_member_id,COUNT(course_video_id) AS cnt FROM (SELECT service_member_id,course_video_id FROM `nurse_edu_course_plan_implement_video` WHERE
CONCAT('0-',video_total_duration)=video_play_recording
GROUP BY service_member_id,course_video_id) AS f GROUP BY service_member_id
) AS t ON t.service_member_id=cpsm.id
WHERE cpsm.exam_actual_end_time IS NOT NULL and ROUND(t.cnt/cv.sumNum,2) = 1
GROUP BY cpsm.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'courseware_exam' as actualType
FROM `nurse_edu_courseware_plan_service_member` AS cpsm
LEFT JOIN nurse_edu_courseware_plan_implement_view AS iv ON iv.service_member_id=cpsm.id AND cpsm.delete_bit=0
WHERE cpsm.exam_actual_end_time IS NOT NULL
GROUP BY cpsm.plan_id
union all
SELECT cpsm.plan_id,count(1) as actualCompletePepoleCount,'courseware_viewed' as actualType
FROM `nurse_edu_courseware_plan_service_member` AS cpsm
LEFT JOIN nurse_edu_courseware_plan_implement_view AS iv ON iv.service_member_id=cpsm.id AND cpsm.delete_bit=0
WHERE IFNULL(iv.view_total_duration,0)>0
GROUP BY cpsm.plan_id
union all
select pp.planId as planId, count(1) as actualCompletePepoleCount,'practice' as actualType
from (
SELECT
(case when IFNULL(ROUND(100 * COUNT(cs.id) / p.all_subject_count, 2), 0) >= p.target_complete_percentage then 'done' else 'undone' end) AS completeStatus,
ip.id as planId
FROM `nurse_edu_practice_plan_service` as p
LEFT JOIN nurse_edu_item_plan AS ip ON p.plan_id = ip.id
LEFT JOIN nurse_edu_practice_plan_service_member AS ppsm ON p.id = ppsm.service_id AND p.delete_bit = '0' AND ppsm.delete_bit = '0'
LEFT JOIN nurse_edu_practice_complete_subject as cs ON cs.service_member_id = ppsm.id
GROUP BY ppsm.member_user_id,planId
) pp where pp.completeStatus='done' GROUP BY planId
) mm on m.id = mm.plan_id and
(case when m.plan_type = 'academic' and mm.actualType = 'academic' then 1
when m.plan_type = 'operate' and mm.actualType = 'operate' then 1
when m.plan_type = 'practice' and mm.actualType = 'practice' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'sign_in' and mm.actualType = 'trainging_sign_in' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'sign_out' and mm.actualType = 'training_sign_out' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'exam' and mm.actualType = 'training_exam' then 1
when m.plan_type ='training' and m.planCompleteCondition = 'questionnaire' and mm.actualType = 'training_questionnaire' then 1
when m.plan_type ='training' and (m.planCompleteCondition = '' or m.planCompleteCondition is null) and mm.actualType = 'training_other' then 1
when m.plan_type = 'course' and m.planCompleteCondition = 'exam' and mm.actualType = 'courseware_exam' then 1
when m.plan_type = 'course' and m.planCompleteCondition = 'viewed' and mm.actualType = 'courseware_viewed' then 1
when m.plan_type = 'courseware' and m.planCompleteCondition = 'exam' and mm.actualType = 'course_exam' then 1
when m.plan_type = 'courseware' and m.planCompleteCondition = 'played' and mm.actualType = 'course_played' then 1
else 0 end) = 1
) xx
where 1=1
ORDER BY xx.create_time desc,xx.last_update_time desc
发表评论