id_, year_, plan_type_, quarter_, month_, week_, start_time_, end_time_, post_id_, post_, org_id_, org_name_, major_id_, major_name_, subject_name_, subject_id_, train_level_, total_num_, reality_num_, hour_, space_id_, space_name_, coach_id_, coach_name_, main_user_id_, main_user_name, way_, train_type_, train_content_, plan_status_, memo_, sn_, is_dele_, create_by_, create_org_id_, create_time_, update_org_id_, update_by_, update_time_, last_time_ SELECT p.plan_type_ as planType, COUNT ( p.* ) AS totalCount, COUNT ( CASE WHEN p.plan_status_ ='1' THEN '1' END ) AS executeCount, COUNT ( CASE WHEN p.plan_status_ = '2' THEN '1' END ) AS finishCount, COUNT ( CASE WHEN p.plan_status_ = '3' THEN '1' END ) AS noFinishCount FROM biz_exam_plan p LEFT JOIN biz_user_train_plan u ON u.plan_id_ = p.id_ WHERE p.is_dele_ ='0' and u.user_id_= #{userId} GROUP BY p.plan_type_ order by p.plan_type_ asc SELECT p.plan_type_ as planType,COUNT ( p.* ) AS totalCount, COUNT ( CASE WHEN p.plan_type_ ='1' THEN '1' END ) AS executeCount, COUNT ( CASE WHEN p.plan_type_ = '2' THEN '1' END ) AS finishCount, COUNT ( CASE WHEN p.plan_type_ = '3' THEN '1' END ) AS noFinishCount FROM biz_exam_plan p LEFT JOIN biz_exam_general_plan g on g.id_ = p.general_plan_id_ WHERE g.plan_status_ != '0' and g.is_dele_='0' and p.is_dele_='0' p.org_id_ = #{vo.orgId} p.year_ = #{vo.year} GROUP BY p.plan_type_ order by p.plan_type_ asc SELECT p.org_name_ as orgName,COUNT ( p.* ) AS totalCount, COUNT ( CASE WHEN p.plan_status_ ='1' THEN '1' END ) AS executeCount, COUNT ( CASE WHEN p.plan_status_ = '2' THEN '1' END ) AS finishCount, COUNT ( CASE WHEN p.plan_status_ = '3' THEN '1' END ) AS noFinishCount FROM biz_exam_plan p LEFT JOIN biz_exam_general_plan g on g.id_ = p.general_plan_id_ WHERE g.plan_status_ != '0' and g.is_dele_='0' and p.is_dele_='0' p.org_id_ = #{vo.orgId} p.year_ = #{vo.year} GROUP BY p.org_name_ SELECT CASE WHEN (p.hour_ - COALESCE(up.hour_, 0)) < 0 THEN 0 ELSE (p.hour_ - COALESCE(up.hour_, 0)) END AS remainingDuration, p.* FROM biz_exam_plan p LEFT JOIN biz_user_train_plan up on up.plan_id_ = p.id_ ${ew.customSqlSegment} SELECT s.* FROM (SELECT s.avgHour as hour,p.* FROM biz_exam_plan p LEFT JOIN ( SELECT up.plan_id_,coalesce(avg(up.hour_),0) as avgHour FROM biz_exam_plan p LEFT JOIN biz_user_train_plan up on up.plan_id_ = p.id_ WHERE p.plan_type_='3' and p.is_dele_='0' GROUP BY up.plan_id_) s on s.plan_id_ = p.id_ WHERE p.plan_type_='3' and p.is_dele_='0') s ${ew.customSqlSegment} SELECT s.* FROM (SELECT p.* FROM biz_exam_plan p LEFT JOIN biz_exam_general_plan g on g.id_ = p.general_plan_id_ WHERE g.plan_status_ != '0' and g.is_dele_='0' and p.is_dele_='0') s ${ew.customSqlSegment}