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 再聚合(求和)