一条SQL统计出所有机构的不同状态的数量

SELECT g.orgname as dw,sum(case when p.personstatus not in ('0','8','9') and p.ENTERTIME between '"+kssj+"' and '"+jssj+"' then 1 else 0 end) as rq,sum(case when p.personstatus in ('8','9') and p.EXITTIME between '"+kssj+"' and '"+jssj+"' then 1 else 0 end) as cq,sum(case when p.personstatus = '8' and p.EXITTIME between '"+kssj+"' and '"+jssj+"' then 1 else 0 end) as lscq FROM tzzf.PERSONS_INVOLED p RIGHT JOIN XT.ORG_INFO g on p.PROCESS_DEPTID = g.orgid and p.isdel = '0' group by g.ORGNAME";

实现思路：通过sum和case when ，如果符合case when 的条件则返回1，通过sum进行汇总，得到总数