2008.06.18 14:39

우리가 일반적으로 점수의 순위는 ODRDER BY desc를 해서 볼수가 있습니다.

하지만 똑같은 점수를 가진 사람들이 많이 나올때..
예를 들면.100, 90, 90, 80 이렇게 점수가 나오면..
순위를 1,2,2,4 이렇게 부여 해야 합니다
 
이거 SQL문으로 할려고 하니 어렵더라구요.. 내 머리가 나쁜건지..

암튼 오라클 8.1.6 버전부터는 아주 쉽게 순위를 부여 할 수 있습니다..

아래 내용을 참고해 보세요...


오라클에서는 RANK Function을 사용해서 순위를 간편하게 부여할 수 있습니다.
 
RANK Function는 oracle 8i(8.1.6) 부터 가능합니다.
8.1.6 이전 버전에서는 사용 할 수 없습니다. ORA-923 error 가 발생 합니다.
 
plsql 내에서는 oracle 9i 부터 가능합니다.  8.1.6에서는 ORA-900 error가 발생 합니다.
 
-- scott유저로 접속을 합니다.
SQLPLUS scott/tiger
 

-- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제 입니다. 
-- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다.

SQL>SELECT empno, ename, sal,
        RANK() OVER (ORDER BY sal DESC ) as rk
        FROM emp;


    EMPNO ENAME             SAL         RK
--------- ---------- ---------- ----------
     7839 KING               5000          1
     7788 SCOTT            3000         2
     7902 FORD              3000          2
     7566 JONES            2975          4
     7698 BLAKE            2850          5
     7782 CLARK            2450          6
     7499 ALLEN            1600          7
     7844 TURNER          1500          8
     7934 MILLER           1300          9
     7521 WARD             1250         10
     7654 MARTIN            1250         10
     7876 ADAMS            1100         12
     7900 JAMES             950         13
     7369 SMITH              800         14



☞ 그룹별로 순위를 부여 하는 법

-- 위 예제는 deptno를 파티션으로 나누어서 부서별로 순위를 부여 합니다.
-- 특정한 그룹별로 순위를 부여하고 싶을때 사용 하면 편합니다.

SQL>SELECT deptno, ename, sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk
       FROM emp ;
 
 DEPTNO ENAME             SAL        RK
------- ---------- ---------- ---------
     10 KING              5000          1
     10 CLARK            2450         2
     10 MILLER           1300         3
     20 SCOTT            3000         1
     20 FORD             3000          1
     20 JONES            2975         3
     20 ADAMS            1100         4
     20 SMITH             800           5
     30 BLAKE            2850          1
     30 ALLEN            1600          2
     30 TURNER           1500        3
     30 WARD             1250          4
     30 MARTIN           1250         4
     30 JAMES             950          6
 


☞ DENSE_RANK() 함수

 DENSE_RANK( ) - 중복 RANK의 수와 무관하게 numbering을 합니다.

-- 1등, 2등, 2등 이렇게 2등이 중복되었는데 4등이 아니라 3등이 부여 됩니다.

SQL>SELECT empno, ename, sal, 
       DENSE_RANK() OVER (ORDER BY sal DESC ) as rk
       FROM emp;    
   
    EMPNO ENAME             SAL        RK
--------- ---------- ---------- ---------
     7839 KING               5000         1
     7788 SCOTT            3000         2
     7902 FORD              3000         2
     7566 JONES            2975         3
     7698 BLAKE             2850         4
     7782 CLARK             2450         5
     7499 ALLEN             1600         6
     7844 TURNER          1500         7
     7934 MILLER            1300         8
     7521 WARD             1250         9
     7654 MARTIN           1250         9
     7876 ADAMS            1100        10
     7900 JAMES             950        11
     7369 SMITH             800        12
  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
신고
Posted by 커피한잔의여유
2008.06.18 14:36

LENGTH 함수와 LENGTHB 함수
 
 

오라클에서 LENGTH 합니다.
 
한글을 2byte로 계산하여 리턴 받으려면

LENTGTHB 함수는 byte 단위로 계산을 합니다.
 
 
 -- LENGTH 함수
SQL>SELECT name, LENGTH(name) LEN  FROM lentest;
 
NAME                        LEN
-------------------- ----------
오라클                        3
oracle                        6
 

 -- LENGTHB 함수
SQL>SELECT name, LENGTHB(name) LEN  FROM  lentest;
 
NAME                        LEN
------------------- ----------
오라클                        6
oracle                        6
 
LENGTHB 함수를 사용해야 합니다.
함수를 사용하면 한글을 1byte로 계산하여 리턴

신고
Posted by 커피한잔의여유
2008.06.18 14:31

select        nvl(S.USERNAME,'Internal') username,
        nvl(S.TERMINAL,'None') terminal,
        L.SID||','||S.SERIAL# Kill,
        U1.NAME||'.'||substr(T1.NAME,1,20) tab,
        decode(L.LMODE,1,'No lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) lmode,
        decode(L.REQUEST,1,'No lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive',null) request
from        V$lock L, 
        V$SESSION S,
        SYS.USER$ U1,
        SYS.OBJ$ T1
where        L.SID = S.SID 
and        T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) 
and        U1.USER# = T1.OWNER#
and        S.TYPE != 'BACKGROUND'
order by 1,2,5

2. lock걸린 잡 삭제

ALTER SYSTEM  KILL SESSION '8, 8589';

신고
Posted by 커피한잔의여유

티스토리 툴바