Mysql 计数与条件或大小写不同

Question

SELECT user_appoint.TreatmentCaseId,
user_appoint.TreatmentCase,
tbl_rank.rank, 
user_appoint.u_id, 
DATE_FORMAT(MAX(user_appoint.ApptDateTime), ("%d-%m-%Y")) AS mdate, 
(CASE user_appoint.TreatmentCase 
WHEN 1 THEN "Open" WHEN 0 THEN "Closed" 
WHEN 2 THEN "Hospitalized" ELSE "" END) AS CaseStatus,
user_det.dob FROM (`user_appoint`) JOIN `user_det` ON 
`user_appoint`.`u_id` = `user_det`.`id` JOIN `tbl_rank` ON 
`user_appoint`.`rank` = `tbl_rank`.`rank_id` WHERE 
`user_appoint`.`comp_id` = '123' AND 
`user_appoint`.`void` = 0 AND 
`user_appoint`.`purpose` = 2 AND 
`TreatmentCaseId` LIKE '%%' 
GROUP BY 
`user_appoint`.`TreatmentCaseId` 
LIMIT 5

这是我的查询，我得到的结果是

   TreatmentCaseId   TreatmentCase  mdate       CaseStatus    dob         

   A11                   2          10-03-2015  Hospitalized  1988-08-20  
   A12                   0          27-11-2014  Closed        1986-08-26  
   A13                   1          26-11-2014  Open          1988-08-20  
   A14                   1          25-11-2014  Open          1988-08-20  

现在我想计算 casestatus 的数量，即我会得到开放=2，关闭=1，住院=1选择的日期是最大日期，因此我根据最大日期选择了该列，在选择该列之后，将计算开放、关闭和住院病例的数量。

Answer 1

您可以使用条件sum:

select sum(CaseStatus = 'Open') as OpenedCount
     , sum(CaseStatus = 'Closed') as ClosedCount
     , sum(CaseStatus = 'Hospitalized') as HospitalizedCount
from (<...>)