case when用法总结

第一种：

SELECT tsi.`id`,tsi.`si_name`,COUNT(*) AS total, SUM(CASE WHEN ts.`GENDER`=0 THEN 1 ELSE 0 END) AS boy, SUM(CASE WHEN ts.`GENDER`=1 THEN 1 ELSE 0 END) AS girl, SUM(CASE WHEN ts.`GENDER`=0 THEN 1 ELSE 0 END)/COUNT(1) bPro, SUM(CASE WHEN ts.`GENDER`=1 THEN 1 ELSE 0 END)/COUNT(1) gPro FROM lh_tm_student ts LEFT JOIN lh_tm_school_info tsi ON tsi.`id` = ts.`DEPT_KEY` WHERE 1 = 1 AND tsi.`id` IS NOT NULL AND tsi.`pid` = #{params.deptId} GROUP BY ts.`DEPT_KEY`

第二种：

select t.*,tpi.project_name as projectName, case t.hs_assessment_type when 1 then 'DR_TV' when 2 then 'CYJ_TV' when 3 then 'PRS_TV' when 4 then 'CS_TV' when 5 then 'CR_TV' when 6 then 'Other_TV' when 7 then 'IR_TV' end as hsAssessmentTypeName, case t.schedule_status when 1 then 'Red' when 2 then 'Yellow' when 3 then 'Green' end as scheduleStatusName, case t.finance_status when 1 then 'Red' when 2 then 'Yellow' when 3 then 'Green' end as financeStatusName, case t.risk_status when 1 then 'Red' when 2 then 'Yellow' when 3 then 'Green' end as riskStatusName, case t.assessment_status when 1 then 'Red' when 2 then 'Yellow' when 3 then 'Green' end as assessmentStatusName, case t.resource_status when 1 then 'Red' when 2 then 'Yellow' when 3 then 'Green' end as resourceStatusName from t_milestone_meeting t left join t_project_info tpi on t.project_id = tpi.id where t.project_id = #{p.projectId}