mybatis中根据同个字段的不同值分别查询统计本月的各个状态的数量

条件：doctorType 有 0 和 1。

目的：我想要分别统计医生类别为0和1的这五种状态

首先，先写出统计这五个状态的sql，你会发现结果是合在一起的，并没有拆分的。

SELECT DISTINCT
        COUNT(CASE WHEN c.consult_status = 2 THEN 1 ELSE NULL END) AS paid,
        COUNT(CASE WHEN c.consult_status = 3 THEN 1 ELSE NULL END) AS received,
        COUNT(CASE WHEN c.consult_status = 4 THEN 1 ELSE NULL END) AS unDiagnosis,
        COUNT(CASE WHEN c.consult_status = 5 THEN 1 ELSE NULL END) AS returned,
        COUNT(CASE WHEN c.consult_status = 6 THEN 1 ELSE NULL END) AS diagnosed
        FROM hz_consult_doctor doc
        LEFT JOIN hz_consult c ON (c.id = doc.consult_id)
        LEFT JOIN hz_hospital hos ON (doc.hospital_id = hos.hospital_id AND doc.is_delete = 0)
        LEFT JOIN hz_consult_patient p ON c.id = p.consult_id
        WHERE 
        c.is_cancel = 0 AND p.is_delete = 0 AND c.is_delete = 0 AND doc.is_delete = 0
		AND c.type = 0
		AND c.union_id = 130123
		AND hos.hospital_id = 1118011814561100810
		AND doc.doctor_id = 1318040811373003377
		AND DATE_FORMAT(c.commit_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

结果应该分别是doctorType为0的一次，为1的一次。所以这不是我们要的结果

现在只需把sql改成以doctorType来分组的就可以得到我们想要的结果。

sql如下：

SELECT DISTINCT doc.doctor_type,-- 这边加上这个为了区别
        COUNT(CASE WHEN c.consult_status = 2 THEN 1 ELSE NULL END) AS paid,
        COUNT(CASE WHEN c.consult_status = 3 THEN 1 ELSE NULL END) AS received,
        COUNT(CASE WHEN c.consult_status = 4 THEN 1 ELSE NULL END) AS unDiagnosis,
        COUNT(CASE WHEN c.consult_status = 5 THEN 1 ELSE NULL END) AS returned,
        COUNT(CASE WHEN c.consult_status = 6 THEN 1 ELSE NULL END) AS diagnosed
        FROM hz_consult_doctor doc
        LEFT JOIN hz_consult c ON (c.id = doc.consult_id)
        LEFT JOIN hz_hospital hos ON (doc.hospital_id = hos.hospital_id AND doc.is_delete = 0)
        LEFT JOIN hz_consult_patient p ON c.id = p.consult_id
        WHERE c.is_cancel = 0 AND p.is_delete = 0 AND c.is_delete = 0 AND doc.is_delete = 0
				AND c.type = 0
				AND c.union_id = 130123
				AND hos.hospital_id = 1118011814561100810
				AND doc.doctor_id = 1318040811373003377
				AND DATE_FORMAT(c.commit_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
				GROUP BY doc.doctor_type -- 以这个doctorType来分组，这样就能得到结果

结果如下：

这样就能区分doctorType的值分别显示什么