select a.orgId,sum(if(empState='A',1,0)) AS 试用,sum(if(empState='B',1,0)) AS 转正, COUNT(*) AS 合计 from 部门表 a left join 员工表 b on a.orgId=b.orgId GROUP BY a.orgId WITH ROLLUP
select a.orgName, sumif(if(empState='A'),1,0) as 试用员工人数, sumif(if(empState='B'),1,0) as 转正员工人数, count(*) as 合计 from 部门表 a,员工表 b where a.orgId=b.orgId and b.empState!='C' group by a.orgName
COUNT(*) AS 合计
from 部门表 a left join 员工表 b on a.orgId=b.orgId GROUP BY a.orgId WITH ROLLUP
sumif(if(empState='A'),1,0) as 试用员工人数,
sumif(if(empState='B'),1,0) as 转正员工人数,
count(*) as 合计
from 部门表 a,员工表 b
where a.orgId=b.orgId
and b.empState!='C'
group by a.orgName