SELECT CUST_NO,COUNT(*),STATUS FROM CUST_DRAFTNO WHERE DRAFT_TYPE IN ('3','4','5')AND STATUS IN ('4','5')
-- AND CUST_NO ='1234'
GROUP BY CUST_NO,STATUS
结果
CUST_NO STATUS COUNT(*)
11111 4 5
11111 5 4
11122 4 4
例如CUST_NO='11111',有4,5两个状态，我想查出CUST_NO='1111'的总条数，以及CUST_NO='1111' AND STATUS='4'的总条数，CUST_NO='1111' AND STATUS='5'的总条数。
查询结果样式为
CUST_NO COUNT1 COUNT2 COUNT3
11111 9 5 4 该如何写呢？
------解决方案--------------------
不是所有的CUST_NO都只有4、5两个状态，对吧？
------解决方案--------------------
select cust_no
,sum(1) count1
,sum(decode(status,4,1,0)) count2
,sum(decode(status,5,1,0)) count3
from cust_draftno
where draft_type in ('3','4','5')
and status in ('4','5')
group by cust_no