[oracle] with절 ( with as) 사용하여 서브쿼리로 조인절과 join하는법

꽤 복잡한(나에게는) 데이터추출 업무를 해야하는 상황이 왔다.

 

문제는, 그냥 inner join, outer join(left)등을 사용하여 on뒤에오는 조건식과 where뒤에 오는 조건식으로만 적절하게 적용하면 되는 형태가 아니었다.

 

예를들어,

학생 한명에게 두명 또는 3명의 선생님이 평가를 내린다고 치자.

학생평가 테이블(APP), 선생님테이블(TEACHER), 평가과목테이블(CODE), 평가진행테이블(ENTIRE), 심사테이블(EVAL) 등등 ... 이모두를 엮는 중심이 되는 seq 는 평가진행테이블이다. 이에 대해 한 학생당 3명 또는 2명의 선생님이 평가를 내렸을시 기존의 쿼리는 아래와 같은 데이터를 뽑아올 수 있도록 작성했다.

 

 

(예시로 임의로 만든 예시용 쿼리)

SELECT
    mng.year AS "년도",
    (   SELECT  code_nm
        FROM  code
        WHERE  code = cate.category_cd
    ) AS "학기",
    (  SELECT  code_nm
        FROM  code
        WHERE  code = app.application_cd
    ) AS "대분류",
    ( SELECT code_nm 
    FROM code
    WHERE code = app.application_cd_detail
    ) AS "소분류",
CASE 
    WHEN APP.SUBJECT LIKE '%평하라%' 
    THEN '~에대해 평하라'
    WHEN APP.SUBJECT LIKE '%논하라%'
    THEN '~에대해 논하라'
    ELSE
        '자유논술'
END as "주제구분",
    app.subject AS "주제명(본인작성)",
    app.name AS "학생명",
    app.step_seq AS "평가 SEQ",
    (SELECT COUNT(step_seq)
        FROM eval
        WHERE step_seq = app.step_seq
            AND not_delete = 'Y'
            AND eval_done = 'Y'
    ) AS "심사개수",
    teacher.name AS "선생님",
    eval.eval_score AS "점수",
    dbms_lob.substr(eval.description, 800) AS "평가내용"
FROM
    whole_entire_step   step
    INNER JOIN ex_mng mng 
    ON ( step.mng_seq = mng.seq
             AND mng.year = '2019' )
    INNER JOIN ex_cate cate
    ON ( step.mng_seq = cate.mng_seq
            AND step.category_seq = cate.seq
            AND cate.catregory IN (  '중간고사'
    ) )
    INNER JOIN ex_app  app 
    ON ( step.seq = app.step_seq  AND app.not_delete = 'Y' )
    INNER JOIN ex_eval eval 
    ON ( app.step_seq = eval.step_seq AND app.not_delete = 'Y' )
    INNER JOIN ex_teacher teacher 
    ON ( teacher.seq = eval.teacher_seq AND teacher.not_delete = 'Y' )

 

결과 (예시)

년도	학기	대분류	소분류	주제구분	주제명(본인작성)	학생명	평가seq	심사개수	선생님	심사점수
2019	1학기	중간고사	논술	~애대해평하라	"00는 무엇인가"	홍길동	109	3	브래드	20
2019	1학기	중간고사	논술	~에대해평하라	"00는 무엇인가"	홍길동	109	3	데이빗	18
2019	1학기	중간고사	논술	~에대해평하라	"00는 무엇인가"	홍길동	109	3	비브	15
2019	1학기	중간고사	논술	~에대해평하라	"00에대한 고찰"	심 청	108	2	피비	20
2019	1학기	중간고사	논술	~에대해평하라	"00에대한 고찰"	심 청	108	2	챈들러	19

위의 쿼리를 실행하면 위의 테이블처럼 심사평이 3개면 3개의 ROW, 2개면 두개의 ROW가 출력된다.

 

이렇게 출력되게 하지않고,

홍길동/109/첫번째선생님: 브래드/20/브래드의평가내용/ 두번째 선샌님: 데이빗/18/ 데이빗의 평가내용~

이렇게 한 ROW에 출력될 수 있도록 하려면 어떻게 JOIN을 해야 할까? 고민했다.

 

일단 서브쿼리에서는 JOIN절에 쓴 ALIAS와의 조건문 적용이 불가하다.

그렇다면 서브쿼리를 사용하면서도 조인절의 테이블과 조건문에 수식을 쓸 수 있게하는 방법은 무엇이 있을까?

구글링 및 선배들의 도움으로 찾게된 것이 바로 "WITH"구문 이었다.

 

WITH구문을 사용한 구문은 아래와 같다..

다 쓰기엔 너무 기니까, 어떻게 WITH절을 적용했는지정도만 알수있는 쿼리를 적어두겠다.

 

SELECT
    app.subject AS "주제명(본인작성)", 
    app.name AS "학생명", 
    app.step_seq AS "평가 SEQ", 

    (WITH tmp AS (
            SELECT
                LISTAGG(teacher.name, '##')
                WITHIN GROUP(ORDER BY  teacher.seq ) AS contents,
                eval.step_seq
            FROM
                ex_teacher   teacher
                LEFT JOIN ex_eval eval ON ( teacher.seq = eval.teacher_seq )
            WHERE
                eval.not_delete = 'Y'
                AND eval.eval_done = 'Y'
                AND teacher.not_delete = 'Y'
            GROUP BY eval.step_seq
        )
        SELECT replace(substr(b.contents, 1, instr(b.contents, '##', 1, 1)), '#', '')
        FROM tmp b
        WHERE  b.step_seq = teacher.step_seq
    ) AS "1번 선생님",
    ( WITH tmp AS (
            SELECT
                LISTAGG(dbms_lob.substr(eval.description, 600), '##') 
                WITHIN GROUP(ORDER BY teacher.seq) AS contents,
                eval.step_seq
            FROM
                ex_teacher   teacher
                LEFT JOIN ex_eval eval ON ( teacher.seq = eval.teacher_seq )
            WHERE
                eval.not_delete = 'Y'
                AND eval.eval_done = 'Y'
                AND teacher.not_delete = 'Y'
            GROUP BY eval.step_seq
        )
        SELECT replace(substr(b.contents, 1, instr(b.contents, '##', 1, 1)), '#', '')
        FROM tmp b
        WHERE  b.step_seq = teacher.step_seq
    ) AS "1번 선생님의 평가내용"

FROM 
    whole_entire_step   step 
    INNER JOIN ex_mng mng  
    ON ( step.mng_seq = mng.seq 
             AND mng.year = '2019' ) 
    INNER JOIN ex_cate cate 
    ON ( step.mng_seq = cate.mng_seq 
            AND step.category_seq = cate.seq 
            AND cate.catregory IN (  '중간고사' 
    ) ) 
    INNER JOIN ex_app  app  
    ON ( step.seq = app.step_seq  AND app.not_delete = 'Y' )

where step.not_delete ='Y' and order by step.seq desc;

 

 

노란색배경으로된 문장만 보자. 이부분이 WITH를 적용한 구문이다
(물론, LISTAGG, substr, instr, dbms_lob.substr등 다양한 내장함수를 사용했다.)

이 포스팅은 with절만 설명하겠다.

 

with구문의 사용방법은 다음과 같다.

WITH ALIAS명 AS(SUB쿼리)
select  컬럼명 from ALIAS명;

 

이렇게 with구문을 적용하면 해당 서브쿼리 자체를 별도의 테이블로 인식하여,

" WHERE  b.step_seq = teacher.step_seq " 처럼 조인절에 쓴 alias와도 결합하여 조건문을 쓸 수 있다.