DB 정렬 관련 쿼리 작성

- 문제

SELECT 'DDDD' AS COD, '1111' AS NAM, null AS DPT
FROM DUAL
UNION ALL
SELECT 'AAAA' AS COD, 'aaaa' AS NAM, '600' AS DPT
FROM DUAL
UNION ALL
SELECT 'CCCC' AS COD, 'xxxx' AS NAM, '300' AS DPT
 FROM DUAL
 UNION ALL
 SELECT 'AAAA' AS COD, '에이이에' AS NAM, '400' AS DPT
 FROM DUAL
 UNION ALL
 SELECT 'CCCC' AS COD, '일일일' AS NAM, '900' AS DPT
 FROM DUAL
 UNION ALL
 SELECT 'BBBB' AS COD, 'twotwo' AS NAM, null AS DPT
 FROM DUAL
 UNION ALL
 SELECT 'BBBB' AS COD, '3333' AS NAM, '800' AS DPT
 FROM DUAL

1. SELECT WITH 처리

2. cod, nam, dpt 순 정렬 처리, 행번호 포함

3. dpt null 값인것을 맨앞으로, 맨뒤로

4. 정렬시 COD가 AAA일때는 이름순, COD가 나머지일때는 부서순








- 쿼리작성
************ 1번, 2번
WITH TEMP
AS
(
    SELECT 'DDDD' AS COD, '1111' AS NAM, NULL AS DPT FROM DUAL
    UNION ALL
    SELECT 'AAAA' AS COD, 'AAAA' AS NAM, '600' AS DPT FROM DUAL
    UNION ALL
    SELECT 'CCCC' AS COD, 'XXXX' AS NAM, '300' AS DPT FROM DUAL
    UNION ALL
    SELECT 'AAAA' AS COD, '에이이에' AS NAM, '400' AS DPT FROM DUAL
    UNION ALL
    SELECT 'CCCC' AS COD, '일일일' AS NAM, '900' AS DPT FROM DUAL
    UNION ALL
    SELECT 'BBBB' AS COD, 'TWOTWO' AS NAM, NULL AS DPT FROM DUAL
    UNION ALL
    SELECT 'BBBB' AS COD, '3333' AS NAM, '800' AS DPT FROM DUAL
)
SELECT ROW_NUMBER() OVER (ORDER BY COD, NAM, DPT ASC) AS RNUM
      ,COD, NAM, DPT
FROM TEMP
--분석함수와 INDEX에 대한 이야기는 할말이 많긴한데, PASS, 직접경험해봐야 알수 있음




************ 3번
WITH TEMP
AS
(
    SELECT 'DDDD' AS COD, '1111' AS NAM, NULL AS DPT FROM DUAL
    UNION ALL
    SELECT 'AAAA' AS COD, 'AAAA' AS NAM, '600' AS DPT FROM DUAL
    UNION ALL
    SELECT 'CCCC' AS COD, 'XXXX' AS NAM, '300' AS DPT FROM DUAL
    UNION ALL
    SELECT 'AAAA' AS COD, '에이이에' AS NAM, '400' AS DPT FROM DUAL
    UNION ALL
    SELECT 'CCCC' AS COD, '일일일' AS NAM, '900' AS DPT FROM DUAL
    UNION ALL
    SELECT 'BBBB' AS COD, 'TWOTWO' AS NAM, NULL AS DPT FROM DUAL
    UNION ALL
    SELECT 'BBBB' AS COD, '3333' AS NAM, '800' AS DPT FROM DUAL
)
SELECT COD, NAM, DPT
FROM TEMP
ORDER BY DPT NULLS FIRST   
--맨앞으로(맨뒤로일때는 FIRST대신 LAST 키워드 사용)



**************** 4번
WITH TEMP
AS
(
    SELECT 'DDDD' AS COD, '1111' AS NAM, NULL AS DPT FROM DUAL
    UNION ALL
    SELECT 'AAAA' AS COD, 'AAAA' AS NAM, '600' AS DPT FROM DUAL
    UNION ALL
    SELECT 'CCCC' AS COD, 'XXXX' AS NAM, '300' AS DPT FROM DUAL
    UNION ALL
    SELECT 'AAAA' AS COD, '에이이에' AS NAM, '400' AS DPT FROM DUAL
    UNION ALL
    SELECT 'CCCC' AS COD, '일일일' AS NAM, '900' AS DPT FROM DUAL
    UNION ALL
    SELECT 'BBBB' AS COD, 'TWOTWO' AS NAM, NULL AS DPT FROM DUAL
    UNION ALL
    SELECT 'BBBB' AS COD, '3333' AS NAM, '800' AS DPT FROM DUAL
)
SELECT COD, NAM, DPT
FROM TEMP
ORDER BY CASE WHEN COD = 'AAAA' THEN NAM ELSE DPT END
-- ORDER BY 절에 CASE, DECODE 가 가능하다.