Oracle ÐÐתÁÐÎÊÌâ

Êý¾Ý¿â²éѯÖÐÄÑÃâ»áÓöµ½ÐÐÁÐת»»µÄÇé¿ö£¬ÕªÁÐһЩ½Ï¾«µäµÄ½â¾ö·½°¸
--####################################################################
Ò»¡¢²ÉÓÃSQL decodeºÍPL/SQLº¯ÊýʵÏÖ
--####################################################################
1¡¢¹Ì¶¨ÁÐÊýµÄÐÐÁÐת»»
Èç
student subject grade
---------------------------
student1 ÓïÎÄ 80
student1 Êýѧ 70
student1 Ó¢Óï 60
student2 ÓïÎÄ 90
student2 Êýѧ 80
student2 Ó¢Óï 100
……
ת»»Îª 
ÓïÎÄ Êýѧ Ó¢Óï
student1 80 70 60
student2 90 80 100
……
Óï¾äÈçÏ£º
select student,sum(decode(subject,'ÓïÎÄ', grade,null)) "ÓïÎÄ",
sum(decode(subject,'Êýѧ', grade,null)) "Êýѧ",
sum(decode(subject,'Ó¢Óï', grade,null)) "Ó¢Óï"
from table
group by student
2¡¢²»¶¨ÁÐÐÐÁÐת»»
Èç
c1 c2
--------------
1 ÎÒ
1 ÊÇ
1 Ë­
2 Öª
2 µÀ
3 ²»
……
ת»»Îª
1 ÎÒÊÇË­
2 ÖªµÀ
3 ²»
ÕâÒ»ÀàÐ͵Äת»»±ØÐë½èÖúÓÚPL/SQLÀ´Íê³É£¬ÕâÀï¸øÒ»¸öÀý×Ó
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER) 
RETURN VARCHAR2 
IS 
Col_c2 VARCHAR2(4000); 
BEGIN
FOR cur IN (SELECT c2 from t WHERE c1=tmp_c1) LOOP 
Col_c2 := Col_c2||cur.c2; 
END LOOP; 
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2; 
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;¼´¿É
ժ¼×Ô£ºhttp://www.360doc.com/showWeb/0/0/18772.aspx 
--####################################################################
¶þ¡¢Ê¹Ó÷ÖÎöº¯Êý½øÐÐÐÐÁÐת»»
--####################################################################
ÆäʵʹÓ÷ÖÎöº¯Êý½øÐд¦ÀíÊǺܺõķ½Ê½£¬·­Ò»ÏÂTomµÄÊ飬½«ÆäÖеÄÒ»¸öÀý×ÓÊÕ¼ÔÚÕâÀï. ±ÈÈç²éѯscott.emp±íµÄÓû§SALÅÅÐòÐÅÏ¢£¬¿ÉÒÔʹÓÃÈçϲéѯ£º
SQL> SELECT deptno, ename,
  2         ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq
  3    from emp;
    DEPTNO ENAME             SEQ
---------- ---------- ----------
  &