-- 1. 사용자 생성
CREATE USER asl IDENTIFIED BY [비밀번호]
DEFAULT tablespace USERS
TEMPORARY tablespace TEMP;
-- 2. 권한부여
GRANT RESOURCE, CONNECT TO asl
2.1. 경기결과 테이블 생성
-- ASL."RESULT" definition
CREATE TABLE "RESULT"
("SEASON" NUMBER NOT NULL ENABLE,
"ROUND" NUMBER NOT NULL ENABLE,
"GAME" NUMBER(1,0) NOT NULL ENABLE,
"PLAYER_1_ID" VARCHAR2(100) NOT NULL ENABLE,
"PLAYER_1_NAME" VARCHAR2(100),
"PLAYER_2_ID" VARCHAR2(100) NOT NULL ENABLE,
"PLAYER_2_NAME" VARCHAR2(100),
"WINNER_ID" VARCHAR2(100),
"DATE" DATE,
"UPPER_SRG" VARCHAR2(100),
CONSTRAINT "RESULT_PK" PRIMARY KEY ("SEASON", "ROUND", "GAME")
);
COMMENT ON COLUMN "RESULT"."SEASON" IS '시즌';
COMMENT ON COLUMN "RESULT"."ROUND" IS '강';
COMMENT ON COLUMN "RESULT"."GAME" IS '경기';
COMMENT ON COLUMN "RESULT"."PLAYER_1_ID" IS '선수1 아이디';
COMMENT ON COLUMN "RESULT"."PLAYER_1_NAME" IS '선수1 이름';
COMMENT ON COLUMN "RESULT"."PLAYER_2_ID" IS '선수2 아이디';
COMMENT ON COLUMN "RESULT"."PLAYER_2_NAME" IS '선수2 이름';
COMMENT ON COLUMN "RESULT"."WINNER_ID" IS '승자 아이디';
COMMENT ON COLUMN "RESULT"."DATE" IS '경기 날짜';
COMMENT ON COLUMN "RESULT"."UPPER_SRG" IS '상위_게임유일값(SEASON_ROURND_GAME)';
COMMENT ON TABLE "RESULT" IS '경기결과';
/*
* 1. WITH절에서 경기별 고유값을 지정
*/
WITH BASE AS (
SELECT SEASON, -- 시즌(9)
ROUND, -- 라운드(8강, 4강, 결승)
SEASON || ROUND || GAME AS SRG, -- 경기별 고유값(시즌 + 라운드 + 경기 )
UPPER_SRG, -- 상위 경기 고유값
GAME,
NVL2(UPPER_SRG,
ROUND || '강 ' || GAME || '경기 - ' || PLAYER_1_NAME || ' VS ' || PLAYER_2_NAME,
'결승 - ' || PLAYER_1_NAME || ' VS ' || PLAYER_2_NAME
) AS TITLE,
PLAYER_1_NAME,
PLAYER_2_NAME
FROM RESULT
)
/*
* 2.경기별 고유값(SRG)와 상위 경기고유값(UPPER_SRG)으로 게층 구현
*/
SELECT SEASON,
ROUND,
SRG,
UPPER_SRG,
GAME,
LPAD(' ', 2*(LEVEL-1)) || TITLE AS LPAD_TITLE, --레벨별 들여쓰기
LEVEL
FROM BASE
START WITH UPPER_SRG IS NULL -- 결승은 상위 경기고유값이 NULL
CONNECT BY PRIOR SRG = UPPER_SRG -- 경기별 고유값과 상위 경기고유값으로 연결
ORDER BY GAME
SELECT 'A' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'C' T1_COL1 FROM DUAL
2번 테이블
SELECT 'A' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'D' T2_COL1 FROM DUAL
1번 테이블과 2번테이블 INNER-JOIN
SELECT T1.T1_COL1, T2.T2_COL1
FROM ( SELECT 'A' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'B' T1_COL1 FROM DUAL UNION ALL
SELECT 'C' T1_COL1 FROM DUAL ) T1,
( SELECT 'A' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'B' T2_COL1 FROM DUAL UNION ALL
SELECT 'D' T2_COL1 FROM DUAL ) T2
WHERE T1.T1_COL1 = T2.T2_COL1