数据库中数据为 部门 项目 性质 年度 1 X A 2003 2 X B 2003 1 Y A 2002 1 X A 2003 2 X B 2002 1 Y A 2002
SQL> select * from t;COL1 COL2 COL3 COL4 ---------- ---------- ---------- -------- 1 X A 2003 2 X B 2003 1 Y A 2002 1 X A 2003 2 X B 2002 1 Y A 2002已选择6行。 SQL> select col1,count1,c1,c2,c3,100*c3/count1 pctA,c4,100*c4/count1 pctB from ( 2 select col1,count(1) count1,sum(decode(col2,'X',1,0)) c1,sum(decode(col2,'Y',1,0)) c2, 3 sum(decode(col3,'A',1,0)) c3,sum(decode(col3,'B',1,0)) c4 4 from t where col4='2003' group by col1) t;COL1 COUNT1 C1 C2 C3 PCTA C4 ---------- ---------- ---------- ---------- ---------- ---------- ---------- PCTB ---------- 1 2 2 0 2 100 0 02 1 1 0 0 0 1 100
部门 项目 性质 年度
1 X A 2003
2 X B 2003
1 Y A 2002
1 X A 2003
2 X B 2002
1 Y A 2002
---------- ---------- ---------- --------
1 X A 2003
2 X B 2003
1 Y A 2002
1 X A 2003
2 X B 2002
1 Y A 2002已选择6行。
SQL> select col1,count1,c1,c2,c3,100*c3/count1 pctA,c4,100*c4/count1 pctB from (
2 select col1,count(1) count1,sum(decode(col2,'X',1,0)) c1,sum(decode(col2,'Y',1,0)) c2,
3 sum(decode(col3,'A',1,0)) c3,sum(decode(col3,'B',1,0)) c4
4 from t where col4='2003' group by col1) t;COL1 COUNT1 C1 C2 C3 PCTA C4
---------- ---------- ---------- ---------- ---------- ---------- ----------
PCTB
----------
1 2 2 0 2 100 0
02 1 1 0 0 0 1
100
不行就要写两个查询了呀