select type , count(1), sum(decode(status,0,1,0)), sum(decode(status,1,1,0)), sum(decode(status,2,1,0)) from tb group by type
测试数据:CREATE TABLE T121 ( TYPE VARCHAR2(20), Status NUMBER(1) ); INSERT INTO T121 VALUES('A', 0); INSERT INTO T121 VALUES('A', 0); INSERT INTO T121 VALUES('A', 1); INSERT INTO T121 VALUES('A', 1); INSERT INTO T121 VALUES('B', 0); INSERT INTO T121 VALUES('B', 1); INSERT INTO T121 VALUES('B', 2); INSERT INTO T121 VALUES('C', 1); INSERT INTO T121 VALUES('C', 2); 测试结果:
count(1),
sum(decode(status,0,1,0)),
sum(decode(status,1,1,0)),
sum(decode(status,2,1,0))
from tb
group by type
(
TYPE VARCHAR2(20),
Status NUMBER(1)
);
INSERT INTO T121 VALUES('A', 0);
INSERT INTO T121 VALUES('A', 0);
INSERT INTO T121 VALUES('A', 1);
INSERT INTO T121 VALUES('A', 1);
INSERT INTO T121 VALUES('B', 0);
INSERT INTO T121 VALUES('B', 1);
INSERT INTO T121 VALUES('B', 2);
INSERT INTO T121 VALUES('C', 1);
INSERT INTO T121 VALUES('C', 2);
测试结果: