sql 取一张表中 不同类型 再根据不同类型分组 取总

SELECT * FROM T_ATM_BOX WHERE `deleted` =0 AND `DamageSts` IN(10,50,30,20,40)

 

DamageSts 

10,50 为 Good

30 为 Damaged

20,40 为 Repairing

 

不同城市  ProjectId 

不同客户  AtmCustomerId

之前写法 分三条sql语句

Good  SELECT ProjectId,AtmCustomerId,COUNT(0) cnt FROM T_ATM_BOX WHERE `deleted` =0 AND `DamageSts` IN(10,50) GROUP BY ProjectId,`AtmCustomerId`

Damaged  SELECT ProjectId,AtmCustomerId,COUNT(0) cnt FROM T_ATM_BOX WHERE `deleted` =0 AND `DamageSts` IN(30) GROUP BY ProjectId,`AtmCustomerId`

Repairing  SELECT ProjectId,AtmCustomerId,COUNT(0) cnt FROM T_ATM_BOX WHERE `deleted` =0 AND `DamageSts` IN(20,40) GROUP BY ProjectId,`AtmCustomerId`

 

137+16+62=215

现在写法

SELECT ProjectId,AtmCustomerId, (CASE t.DamageSts
WHEN 10 THEN 'Good'
WHEN 50 THEN 'Good'
WHEN 30 THEN 'Damaged'
WHEN 20 THEN 'Repairing'
WHEN 40 THEN 'Repairing'
ELSE ''
END) Statu FROM T_ATM_BOX t WHERE `deleted` =0 AND DamageSts IN(10,50,30,20,40)

 

 

 

SELECT ProjectId,AtmCustomerId, (CASE t.DamageSts
WHEN 10 THEN 'Good'
WHEN 50 THEN 'Good'
WHEN 30 THEN 'Damaged'
WHEN 20 THEN 'Repairing'
WHEN 40 THEN 'Repairing'
ELSE ''
END) Statu,COUNT(0) cnt FROM T_ATM_BOX t WHERE `deleted` =0 AND DamageSts IN(10,50,30,20,40) GROUP BY ProjectId,AtmCustomerId,Statu

 

用一层 case when then 把不同类型(10,50) (30) (20,40) 转成同种类型 Good  Damaged  Repairing

再GROUP BY 再聚合(求和)