개발자인생

SELECT  TRUNC(SYSDATE,'MONTH') FROM DUAL

UNION ALL

SELECT  TRUNC(SYSDATE,'MONTH')-1 FROM DUAL

UNION ALL

SELECT NEXT_DAY((TRUNC(SYSDATE,'MONTH')-1),'월') FROM DUAL

2014/03/01 00:00:00

2014/02/28 00:00:00

2014/03/03 00:00:00

SELECT  SYSDATE

        , TRUNC(SYSDATE) 

        , TRUNC(SYSDATE,'YEAR')

        , TRUNC(SYSDATE,'MONTH')

        , TRUNC(SYSDATE,'DAY')

        , TRUNC(12543.345,2)

        , TRUNC(12543.345,1)

        , TRUNC(12543.345)

FROM DUAL

2014/03/17 19:38:40

2014/03/17 00:00:00

2014/01/01 00:00:00

2014/03/01 00:00:00

2014/03/16 00:00:00

12543.34

12543.3

12543

with emp  as (

select '7782' EMPNO, 'CLARK' ENAME  , 2450 SAL  ,'10' DEPTNO from dual union all

select '7839', 'KING'    , 5000   ,'10' from dual union all

select '7934', 'MILLER'  , 1300   ,'10' from dual union all

select '7566', 'JONES'   , 2975   ,'20' from dual union all

select '7902', 'FORD'    , 3000   ,'20' from dual union all

select '7876', 'ADAMS'   , 1100   ,'20' from dual union all

select '7369', 'SMITH'   , 800    ,'20' from dual union all

select '7788', 'SCOTT'   , 3000   ,'20' from dual union all

select '7521', 'WARD'    , 1250   ,'30' from dual union all

select '7844', 'TURNER'  , 1500   ,'30' from dual union all

select '7499', 'ALLEN'   , 1600   ,'30' from dual union all

select '7900', 'JAMES'   , 950    ,'30' from dual union all

select '7698', 'BLAKE'   , 2850   ,'30' from dual union all

select '7654', 'MARTIN'  , 1250   ,'30' from dual 

)

SELECT  EMPNO

        , ENAME

        , A.DEPTNO

        , RANK() OVER ( ORDER BY SAL DESC)         RNK

        , DENSE_RANK() OVER ( ORDER BY SAL DESC)   D_RNK

        , ROW_NUMBER() OVER ( ORDER BY SAL DESC)   R_RNK

FROM    EMP A

WHERE   DEPTNO = '30'

EMPNO   ENAME   DEPTNO  RNK     D_RNK   R_RNK

7698         BLAKE 30    1           1           1

7499         ALLEN 30    2           2           2

7844         TURNER 30    3           3           3

7654         MARTIN 30    4           4           4

7521         WARD 30    4           4           5

7900         JAMES 30    6           5           6

SELECT  EMPNO

        , ENAME

        , DEPTNO

        , RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)         RNK

        , DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)   D_RNK

        , ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)   R_RNK

FROM    EMP

EMPNO   ENAME   DEPTNO  RNK     D_RNK   R_RNK

7839         KING         10         1 1 1

7782         CLARK 10         2 2 2

7934         MILLER 10         3 3 3

7902         FORD 20         1 1 1

7788         SCOTT 20         1 1 2

7566         JONES 20         3 2 3

7876         ADAMS 20         4 3 4

7369         SMITH 20         5 4 5

7698         BLAKE 30         1 1 1

7499         ALLEN 30         2 2 2

7844         TURNER 30         3 3 3

7521         WARD 30         4 4 4

7654         MARTIN 30         4 4 5

7900         JAMES 30         6 5 6