最近写项目的时候碰到要在一张表下去统计多个不同状态的数量,想着可不可以使用一条SQL返回多个数据,我把自己的例子给大家参考一下:
SELECT
sum( CASE acl.counting_status WHEN 'NEW' THEN 1 ELSE 0 END ) AS unActualQty,
sum( CASE acl.counting_status WHEN 'DIFFERENT' THEN 1 ELSE 0 END ) AS diffQty,
sum( CASE acl.counting_status WHEN 'LOSS' THEN 1 ELSE 0 END ) AS lossQty,
sum( CASE acl.counting_status WHEN 'MATCHED' THEN 1 ELSE 0 END ) AS amtActualQty,
sum( CASE acl.counting_status WHEN 'OVER_RAGE' THEN 1 ELSE 0 END ) AS profitQty,
count( 1 ) AS totalQty
FROM acnt_counting_line acl
查询的结果如下: