with temp as( select 'A1' c1,'B1' c2,'C1' c3,0 flag,1 num from dual union all select 'A1' c1,'B1' c2,'C1' c3,0 flag,5 num from dual union all select 'A1' c1,'B1' c2,'C1' c3,1 flag,4 num from dual union all select 'A1' c1,'B1' c2,'C1' c3,1 flag,6 num from dual union all select 'A1' c1,'B1' c2,'C1' c3,1 flag,8 num from dual union all select 'A1' c1,'B1' c2,'C1' c3,0 flag,12 num from dual union all select 'A1' c1,'B1' c2,'C1' c3,0 flag,10 num from dual ) select c1,c2,c3,flag0*100/(flag0+flag1)||'%',flag1*100/(flag0+flag1)||'%' from ( select c1,c2,c3,sum(decode(flag,0,num)) flag0,sum(decode(flag,1,num)) flag1 from temp group by c1,c2,c3 )
SELECT A,B,C,SUM(GESHU) SUM_COL,SUM(DECODE(FLAG,0,GESHU,0)) FLAG_0,SUM(DECODE(FLAG,1,GESHU,0)) FLAG_1,SUM(DECODE(FLAG,0,GESHU,0))/SUM(GESHU)*100 SUN_FALG0,SUM(DECODE(FLAG,1,GESHU,0))/SUM(GESHU)*100 SUN_FALG0 FROM TAB1 GROUP BY A,B,C; 建表语句 CREATE TABLE TAB1(A VARCHAR2(10),B VARCHAR2(10),C VARCHAR2(10),FLAG VARCHAR2(10),GESHU VARCHAR2(10)); INSERT INTO TAB1 VALUES('A1','B1','C1','0','1'); INSERT INTO TAB1 VALUES('A1','B1','C1','0','5'); INSERT INTO TAB1 VALUES('A1','B1','C1','1','4'); INSERT INTO TAB1 VALUES('A1','B1','C2','0','8');
SCOTT@oamis>>l 1 SELECT A,B,C,SUM(GESHU) SUM_COL,SUM(DECODE(FLAG,0,GESHU,0)) FLAG_0,SUM(DECO DE(FLAG,1,GESHU,0)) FLAG_1,SUM(DECODE(FLAG,0,GESHU,0))/SUM(GESHU)*100 SUN_FALG0, SUM(DECODE(FLAG,1,GESHU,0))/SUM(GESHU)*100 SUN_FALG0 2 FROM TAB1 3* GROUP BY A,B,C SCOTT@oamis>>/A B C SUM_COL FLAG_0 FLAG_1 SUN_FALG0 SU N_FALG0 ---------- ---------- ---------- ---------- ---------- ---------- ---------- --- A1 B1 C2 8 8 0 100 0 A1 B1 C1 10 6 4 60 40已用时间: 00: 00: 00.03
SQL> select a,b,c,sum(ncount), 2 sum(decode(nflag,0,ncount,0)), 3 sum(decode(nflag,1,ncount,0)), 4 sum(decode(nflag,0,ncount,0))/sum(ncount)*100, 5 sum(decode(nflag,1,ncount,0))/sum(ncount)*100 6 from test 7 group by a,b,c;
select 'A1' c1,'B1' c2,'C1' c3,0 flag,1 num from dual
union all
select 'A1' c1,'B1' c2,'C1' c3,0 flag,5 num from dual
union all
select 'A1' c1,'B1' c2,'C1' c3,1 flag,4 num from dual
union all
select 'A1' c1,'B1' c2,'C1' c3,1 flag,6 num from dual
union all
select 'A1' c1,'B1' c2,'C1' c3,1 flag,8 num from dual
union all
select 'A1' c1,'B1' c2,'C1' c3,0 flag,12 num from dual
union all
select 'A1' c1,'B1' c2,'C1' c3,0 flag,10 num from dual
)
select c1,c2,c3,flag0*100/(flag0+flag1)||'%',flag1*100/(flag0+flag1)||'%' from (
select c1,c2,c3,sum(decode(flag,0,num)) flag0,sum(decode(flag,1,num)) flag1 from temp
group by c1,c2,c3
)
FROM TAB1
GROUP BY A,B,C;
建表语句
CREATE TABLE TAB1(A VARCHAR2(10),B VARCHAR2(10),C VARCHAR2(10),FLAG VARCHAR2(10),GESHU VARCHAR2(10));
INSERT INTO TAB1 VALUES('A1','B1','C1','0','1');
INSERT INTO TAB1 VALUES('A1','B1','C1','0','5');
INSERT INTO TAB1 VALUES('A1','B1','C1','1','4');
INSERT INTO TAB1 VALUES('A1','B1','C2','0','8');
1 SELECT A,B,C,SUM(GESHU) SUM_COL,SUM(DECODE(FLAG,0,GESHU,0)) FLAG_0,SUM(DECO
DE(FLAG,1,GESHU,0)) FLAG_1,SUM(DECODE(FLAG,0,GESHU,0))/SUM(GESHU)*100 SUN_FALG0,
SUM(DECODE(FLAG,1,GESHU,0))/SUM(GESHU)*100 SUN_FALG0
2 FROM TAB1
3* GROUP BY A,B,C
SCOTT@oamis>>/A B C SUM_COL FLAG_0 FLAG_1 SUN_FALG0 SU
N_FALG0
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
A1 B1 C2 8 8 0 100
0
A1 B1 C1 10 6 4 60
40已用时间: 00: 00: 00.03
2 sum(decode(nflag,0,ncount,0)),
3 sum(decode(nflag,1,ncount,0)),
4 sum(decode(nflag,0,ncount,0))/sum(ncount)*100,
5 sum(decode(nflag,1,ncount,0))/sum(ncount)*100
6 from test
7 group by a,b,c;
decode可以做哪些事情呢?
我的flag字段需要在一个范围内,如:flag=1,2,3的个数,而不是简单的flag=1的个数。
decode可以做吗
decode相当于高级编程语言中的if。else语句。如果你的flag范围值比较多的话,可以用case when语句。
原来这样,decode真是强大。