今天在网上看到这样的题:
http://www.oracle.com.cn/viewthread.php?tid=124762&pid=978459&page=1&extra=page%3D1#pid978459
3.有一表a,
type year total
t1 2006 100
t1 2007 40
t2 2006 50
t2 2007 60
请按照以下形式输出:
type year(2006) year(2007)
t1 100 40
t2 50 60
5.
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
请按照以下形式输出:
胜 负
2005-05-09 2 2
2005-05-10 1 2
----------------------------
SQL> SELECT * FROM C;
TYPE_C YEAR_C TOTAL_C
------ ---------- ----------
T1 2006 100
T1 2007 40
T2 2006 50
T2 2007 60
SQL>
SQL> SELECT
2 DECODE(TRIM(TYPE_C),'T1','T1','T2')"T" ,
3 SUM(DECODE(TRIM(YEAR_C),'2006',TOTAL_C)) "2006",
4 SUM(DECODE(TRIM(YEAR_C),'2007',TOTAL_C))"2007"
5 FROM C GROUP BY TYPE_C
6 /
T 2006 2007
-- ---------- ----------
T1 100 40
T2 50 60
-------------
SQL> select * from d;
DATA_C VV
---------------------------------------- ----------------------------------------
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
7 rows selected
SQL>
SQL> select
2 decode(data_c,'2005-05-09','2005-05-09','2005-05-10'),
3 sum(decode (vv,'胜',1,0)),
4 sum(decode (vv,'负',1,0))
5 from d
6 group by data_c
7 /
DECODE(DATA_C,'2005-05-09','20 SUM(DECODE(VV,'胜',1,0)) SUM(DECODE(VV,'负',1,0))
------------------------------ ------------------------ ------------------------
2005-05-09 2 2
2005-05-10 1 2
--------------------------
总结:
1.decode用法就是使行列互换(当然在别的DBMS里边有别外的函数)
2.如果说是字符型来取值来批配那就要去掉空格
3.记住SUM 起来会比较好看一些