2024-04-03 [SQL PROGRAMMER 문제 풀이]

CASE (취소되지 않은 진료 예약 조회하기)

환자 정보를 담은 (PATIENT) 테이블과 의사 정보를 담은 (DOCTOR) 테이블, 그리고 진료 예약목록을 담은 (APPOINTMENT)에 대한 테이블입니다.

 

PATIENT 테이블의 구조는 아래와 같으며,

PT_NO, PT_NAME, GEND_CD, AGE, TLNO는 각각 환자번호, 환자이름, 성별코드, 나이, 전화번호를 의미

PATIENT TABLE STRUCTURE

 

DOCTOR 테이블 구조는 아래와 같으며,

DR_NAME, DR_ID, LCNS_NO, HIRE_YMD, MCDP_CD, TLNO는 각각 의사이름, 의사ID, 면허번호, 고용일자, 진료과코드, 전화번호를 의미

DOCTOR TABLE STRUCTURE

 

APPOINTMENT 테이블은 아래와 같으며,

APNT_YMD, APNT_NO, PT_NO, MCDP_CD, MDDR_ID, APNT_CNCL_YN, APNT_CNCL_YMD는 각각 진료 예약일시, 진료예약번호, 환자번호, 진료과코드, 의사ID, 예약취소여부, 예약취소날짜를 의미

APPOINTMENT TABLE STRUCTURE

 

문제

PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서

2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성.

진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력.

결과는 진료예약일시를 기준으로 오름차순 정렬.

 

1. 풀이 및 해설

처음 아래와 같이 쿼리를 작성해보았다.

SELECT APNT_NO,
       PT_NAME,
       J.PT_NO,
       MCDP_CD,
       DR_NAME,
       APNT_YMD
FROM
(
SELECT D.DR_NAME,
        A.APNT_NO,
        A.MCDP_CD,
        A.APNT_YMD,
        A.PT_NO
FROM DOCTOR D JOIN APPOINTMENT A ON D.DR_ID=A.MDDR_ID
WHERE D.MCDP_CD = 'CS' AND A.APNT_YMD LIKE '2022-04-13%' AND A.APNT_CNCL_YN = 'N'
) J INNER JOIN PATIENT P ON J.PT_NO=P.PT_NO
ORDER BY APNT_YMD

1. DOCTOR 테이블과 APPOINTMENT 테이블 내 공통적으로 의미하는 컬럼(의사ID)을 JOIN하여

A 테이블 내 진료과코드가 CS이면서 예약일자가 2022년 4월 13일이며, 예약이 취소되지 않은 로우를 찾기위해 WHERE 조건문을 위와 같이 활용,

2. 의사이름을 출력하기 위해 SubQuery 내 의사이름 컬럼을 함께 포함하였다. (추후 PATIENT 테이블과 JOIN할 때 필요한 컬럼들을 SELECT문 안에 포함)

3. 다음 구문을 서브쿼리로 묶고 J(JOIN)라는 Alias로 지칭 후 PT_NO컬럼 활용 PATIENT 테이블과 JOIN

4. 문제에서 요구하는 출력 컬럼을 SELECT문 내 입력 후 진료예약일시를 기준으로 오름차순 정렬하여 정답이 출력되었다.

 

세가지 테이블을 JOIN하는 경우는 처음이라 조금 복잡하기도 하고,

'SQL은 관계형 데이터베이스로 실무에서 많은 테이블들을 JOIN하는 경우가 많겠구나' 싶어서 더 간단하게, 아니면 가독성이 높게 쿼리를 작성하는 방법이 있을까 싶어 추가적으로 다른 쿼리 작성 방법을 알아보았다.

 

2. 풀이 및 해설 (WITH문 활용)

• 먼저 SQL에서 WITH문은 무엇을 의미하고, 언제 활용할까?

Query을 작성하다보면 SubQuery를 사용해야 하는 경우가 많고, 한 두개만 사용하면 괜찮지만 수가 많아질 수록 가독성이 떨어지며, 재사용할 수 없어서 매번 같은 Query를 작성해줘야 한다.

WITH문을 통해서 다음과 같은 문제점을 해결하는데 도움을 줄 수 있다.

 

2-1. WITH문이란?

   1-1. 이름을 가진 SubQuery를 정의한 후 사용하는 구문

   1-2. Query의 전체적인 가독성을 높이고, 재사용이 가능하다는 장점이 있음

   1-3. 대부분의 DBMS에서 지원

   1-4. 계층형쿼리를 구현 가능

         - WITH순환절 (* 오라클은 11g 버전부터 계층형쿼리를 위한 CONNECT BY절을 지원)

   1-5. 오라클에서는 한번만 사용되면 Inline View, 두번이상 사용되면 Materialize View로 처리

         - 한번도 사용하지 않으면 [ORA-01762] 오류가 발생

         - /*+ Materialize */ 힌트로 Inline View를 Materialize View로 만들 수 있음

 

2-2. 기본구조

 WITH [ 별명 ] AS ( SUB QUERY )

   - 컬럼명은 생략 가능

   - 쉼표(,)로 구분하여 여러개를 정의할 수 있음

   - 먼저 생성된 SubQuery는 나중에 생성하는 SubQuery에서 사용 가능

     (EX. [ 별명2 ]에서 [ 별명1 ]을 사용 가능)

 

WITH문을 활용하여 작성한 쿼리는 아래와 같다.

WITH NOT_CANCEL AS (
    SELECT APNT_YMD, APNT_NO, PT_NO, MCDP_CD, MDDR_ID
    FROM APPOINTMENT
    WHERE 1=1 
        AND
        APNT_CNCL_YN = 'N' 
        AND
        APNT_YMD LIKE '2022-04-13%'
)

SELECT NC.APNT_NO, P.PT_NAME, NC.PT_NO, NC.MCDP_CD, D.DR_NAME, NC.APNT_YMD
FROM NOT_CANCEL NC INNER JOIN PATIENT P 
ON NC.PT_NO = P.PT_NO
    INNER JOIN DOCTOR D
    ON NC.MDDR_ID = D.DR_ID
ORDER BY NC.APNT_YMD

1. WITH문 활용 4월 13일 예약을 취소하지 않은 예약정보를 담은 TABLE을 NOT_CANCEL 별명으로 새로운 테이블을 생성,

2. 이후 첫번째 풀이와 동일한 방법으로 풀이 해석가능

 

그리고 여기서 WHERE절 내 1=1은 무엇을 의미하는 걸까?

WHERE는 조회하려는 데이터들의 조건을 거는 문법인데, 1 = 1은 말그대로 참을 의미

그래서 "아래와 같은 조건들을 충족할 때, 모두(참) 조회한다"는 의미

 

WITH문을 활용 실습해보고자 다른 쿼리를 작성해보았다!

 

3. 풀이 및 해설 (WITH문 실습)

WITH NOT_CANCEL AS (
    SELECT A.APNT_YMD,
           A.APNT_NO,
           A.PT_NO,
           A.MCDP_CD,
           A.MDDR_ID,
           D.DR_NAME
    FROM APPOINTMENT A JOIN DOCTOR D ON A.MDDR_ID=D.DR_ID
    WHERE 1=1 
        AND
        A.APNT_CNCL_YN = 'N'
        AND
        A.APNT_YMD LIKE '2022-04-13%')
        
SELECT NC.APNT_NO, P.PT_NAME, NC.PT_NO, NC.MCDP_CD, NC.DR_NAME, NC.APNT_YMD
FROM NOT_CANCEL NC JOIN PATIENT P ON NC.PT_NO = P.PT_NO
ORDER BY NC.APNT_YMD

WITH문 내 JOIN기능을 활용 사전에 예약정보를 담은 테이블과 의사정보를 담은 테이블을 JOIN하여 재사용할 수 있는 새로운 테이블을 생성

 

참조 링크

- https://heodolf.tistory.com/71