0813_Oracle : 상호 연관 서브 쿼리

예제를 통해 알아보자.

 

예제를 위한 테이블 생성 및 자료 삽입

CREATE TABLE grade1 
(
    grade VARCHAR2(10) PRIMARY KEY,
    score NUMBER(3)
);
INSERT INTO grade1 ( grade, score ) VALUES ('A', 90);
INSERT INTO grade1 ( grade, score ) VALUES ('B', 80);
INSERT INTO grade1 ( grade, score ) VALUES ('C', 70);
INSERT INTO grade1 ( grade, score ) VALUES ('D', 60);
INSERT INTO grade1 ( grade, score ) VALUES ('F', 0);
COMMIT;

CREATE TABLE score1
(
    hak VARCHAR2(10) PRIMARY KEY,
    score NUMBER(3)
);

INSERT INTO score1 ( hak, score ) VALUES('1', 75);
INSERT INTO score1 ( hak, score ) VALUES('2', 50);
INSERT INTO score1 ( hak, score ) VALUES('3', 90);
INSERT INTO score1 ( hak, score ) VALUES('4', 80);
INSERT INTO score1 ( hak, score ) VALUES('5', 65);
COMMIT;

hak	score	gscore
1	75	C
2	50	F
3	90	A
4	80	B
5	65	D

위의 테이블형태로 Oracle에서 출력하고 싶다.

 

먼저

hak	score	gscore
1	75	70
2	50	0
3	90	90
4	80	80
5	65	60

위 형태를 출력해보자.

SELECT hak, score, 
(SELECT MAX(score) FROM grade1 WHERE score <= score1.score) gscore
FROM score1;

SELECT hak, s1.score, grade FROM (
    SELECT hak, score, 
        (SELECT MAX(score) FROM grade1 WHERE score <= score1.score) gscore
    FROM score1
) s1
JOIN grade1 s2 ON s1.gscore = s2.score
ORDER BY hak;