데이터베이스 질문과 답변
테이블은 아래와 같고 기본키는 (학교/학번/과목/시험방법) 입니다.
-------------------------------------------------------------------------------------
학교 학번 과목 시험방법 시험시작일 시험상태
-------------------------------------------------------------------------------------
AAA 001 국어 A 2000-01-01 시험준비
AAA 001 국어 B 2000-03-03 시험준비
AAA 001 국어 C 2000-08-08 시험준비
AAA 001 국어 D 2000-11-11 시험준비
BBB 002 영어 A 2000-02-02 시험완료
BBB 002 영어 B 2000-04-01 시험준비
BBB 002 영어 C 2000-06-06 시험준비
BBB 002 영어 D 2000-08-08 시험준비
CCC 003 일어 A 2000-01-02 시험완료
CCC 003 일어 B 2000-01-03 시험준비
CCC 003 일어 C 2000-01-04 시험준비
CCC 003 일어 D 2000-01-05 시험준비
------------------------------------------------------------------------------------
이제 누군가 시험을 보기 시작했다면
위 테이블에 영어에서 보시듯 영어에 A시험방법을 완료했다면 상태값은 [시험완료]로 업데이트가 되고
B, C, D는 시험준비 그대로 입니다.
B시험이 진행중이라면 상태는 [시험중]이 됩니다. 아직 C, D는 아무것도 안했으니 시험준비로 남아 있게 됩니다.
이런 식으로 상태값은 진행을 합니다.
( * 중요한것은 A단계가 완료가 되어야 B단계가 진행 될 수 있고 C, D 역시 마찬가지입니다.
A시험방법이 완료가 안되었는데 B가 진행중이나 완료가 될 수 없고
B가 완료가 안되었는데 C가 진행중이나 완료가 될 수 없다는 뜻입니다.
각 시험방법은 순차적으로 진행이 됩니다.)
이제 하고 싶은작업은 어떤 사람이 특정일로 조회를 했을때
이 입력일을 기준으로 각 과목에 시험진행상태가 어떻게 되었는가 파악하는 부분입니다.
잘 진행되고 있는지 완료되었는지 지연되고 있는지입니다.
* 실제 시험의 완료란 D방법까지 완료가 되면 그것은 시험완료입니다.
어떤 사람이 2000-04-04 를 입력했다면
국어 과목의 경우 "지연" 입니다.
A시험방법은 2000-01-01 ~ 2000-03-02 시험준비 <-- 2000-04-04기준으로 이미 완료여야 하는데 시험준비므로 "지연"
B시험방법은 2000-03-03 ~ 2000-08-07 시험준비 <-- 2000-04-04기준
C시험방법은 2000-08-08 ~ 2000-11-10 시험준비
D시험방법은 2000-11-11 ~ 시험준비
위에 영어 과목은 "시험준비" 상태입니다.
A시험방법은 2000-02-02 ~ 2000-03-31 시험완료 <-- 2000-04-04기준으로 차질없이 이미 완료되었으며
B시험방법은 2000-04-01 ~ 2000-06-05 시험준비 <-- 2000-04-04기준으로 A는 시험이끝났음. 차질없이 "시험준비"
C시험방법은 2000-06-06 ~ 2000-08-08 시험준비
D시험방법은 2000-08-08 ~ 시험준비
일어의 경우 "지연" 입니다.
A시험방법은 2000-01-02 ~ 2000-01-02 시험완료
B시험방법은 2000-01-03 ~ 2000-01-03 시험준비
C시험방법은 2000-01-04 ~ 2000-01-04 시험준비
D시험방법은 2000-01-05 ~ 시험준비 <-- 2004-04-04 한참 전인데 아직도 완료되지 않았으므로 "지연"
쉽지가 않네요 ㅠㅠ
WITH t(학교, 학번, 과목, 시험방법, 시험시작일, 시험상태) AS
(
SELECT 'AAA', '001', '국어', 'A', '2000-01-01', '시험준비' FROM dual
UNION ALL SELECT 'AAA', '001', '국어', 'B', '2000-03-03', '시험준비' FROM dual
UNION ALL SELECT 'AAA', '001', '국어', 'C', '2000-08-08', '시험준비' FROM dual
UNION ALL SELECT 'AAA', '001', '국어', 'D', '2000-11-11', '시험준비' FROM dual
UNION ALL SELECT 'BBB', '002', '영어', 'A', '2000-02-02', '시험완료' FROM dual
UNION ALL SELECT 'BBB', '002', '영어', 'B', '2000-04-01', '시험준비' FROM dual
UNION ALL SELECT 'BBB', '002', '영어', 'C', '2000-06-06', '시험준비' FROM dual
UNION ALL SELECT 'BBB', '002', '영어', 'D', '2000-08-08', '시험준비' FROM dual
UNION ALL SELECT 'CCC', '003', '일어', 'A', '2000-01-02', '시험완료' FROM dual
UNION ALL SELECT 'CCC', '003', '일어', 'B', '2000-01-03', '시험준비' FROM dual
UNION ALL SELECT 'CCC', '003', '일어', 'C', '2000-01-04', '시험준비' FROM dual
UNION ALL SELECT 'CCC', '003', '일어', 'D', '2000-01-05', '시험준비' FROM dual
UNION ALL SELECT 'DDD', '003', '수학', 'A', '2000-04-29', '시험완료' FROM dual
UNION ALL SELECT 'DDD', '003', '수학', 'B', '2000-05-04', '시험완료' FROM dual
UNION ALL SELECT 'DDD', '003', '수학', 'C', '2000-05-20', '시험준비' FROM dual
UNION ALL SELECT 'DDD', '003', '수학', 'D', '2000-05-30', '시험준비' FROM dual
UNION ALL SELECT 'EEE', '003', '독어', 'A', '2000-01-29', '시험완료' FROM dual
UNION ALL SELECT 'EEE', '003', '독어', 'B', '2000-02-01', '시험완료' FROM dual
UNION ALL SELECT 'EEE', '003', '독어', 'C', '2000-02-08', '시험완료' FROM dual
UNION ALL SELECT 'EEE', '003', '독어', 'D', '2000-02-28', '시험완료' FROM dual
UNION ALL SELECT 'FFF', '002', '사회', 'A', '2000-02-02', '시험완료' FROM dual
UNION ALL SELECT 'FFF', '002', '사회', 'B', '2000-04-01', '시험완료' FROM dual
UNION ALL SELECT 'FFF', '002', '사회', 'C', '2000-06-06', '시험준비' FROM dual
UNION ALL SELECT 'FFF', '002', '사회', 'D', '2000-08-08', '시험준비' FROM dual
UNION ALL SELECT 'GGG', '002', '불어', 'A', '2000-04-08', '시험완료' FROM dual
UNION ALL SELECT 'GGG', '002', '불어', 'B', '2000-05-09', '시험완료' FROM dual
UNION ALL SELECT 'GGG', '002', '불어', 'C', '2000-06-06', '시험완료' FROM dual
UNION ALL SELECT 'GGG', '002', '불어', 'D', '2000-08-08', '시험완료' FROM dual
UNION ALL SELECT 'HHH', '002', '체육', 'A', '2000-02-02', '시험완료' FROM dual
UNION ALL SELECT 'HHH', '002', '체육', 'B', '2000-04-01', '시험중' FROM dual
UNION ALL SELECT 'HHH', '002', '체육', 'C', '2000-06-06', '시험준비' FROM dual
UNION ALL SELECT 'HHH', '002', '체육', 'D', '2000-08-08', '시험준비' FROM dual
)
SELECT 학교, 학번, 과목
, MIN(시험상태_A) 시험상태_A
, MIN(시험상태_B) 시험상태_B
, MIN(시험상태_C) 시험상태_C
, MIN(시험상태_D) 시험상태_D
, NVL(MAX(
CASE WHEN 시험상태 != '시험완료'
THEN CASE WHEN 시험종료일 <= '2000-04-04' THEN '지연'
WHEN 시험종료일 > '2000-04-04' THEN 시험상태
END
END), '시험완료') 시험상태
FROM (
SELECT 학교, 학번, 과목
, CASE 시험방법 WHEN 'A' THEN 시험상태 END 시험상태_A
, CASE 시험방법 WHEN 'B' THEN 시험상태 END 시험상태_B
, CASE 시험방법 WHEN 'C' THEN 시험상태 END 시험상태_C
, CASE 시험방법 WHEN 'D' THEN 시험상태 END 시험상태_D
, 시험상태
, 시험방법
, 시험시작일
, LEAD(시험시작일, 1, '9999-12-31')
OVER(PARTITION BY 학교, 학번, 과목 ORDER BY 시험방법) 시험종료일
FROM t
)
GROUP BY 학교, 학번, 과목
ORDER BY 학교, 학번, 과목
;
정말 대단합니다. 할 말을 잃었음..ㅎㅎ
WITH t(학교, 학번, 과목, 시험방법, 시험시작일, 시험상태) AS
(
SELECT 'AAA', '001', '국어', 'A', '2000-01-01', '시험준비'
UNION ALL SELECT 'AAA', '001', '국어', 'B', '2000-03-03', '시험준비'
UNION ALL SELECT 'AAA', '001', '국어', 'C', '2000-08-08', '시험준비'
UNION ALL SELECT 'AAA', '001', '국어', 'D', '2000-11-11', '시험준비'
UNION ALL SELECT 'BBB', '002', '영어', 'A', '2000-02-02', '시험완료'
UNION ALL SELECT 'BBB', '002', '영어', 'B', '2000-04-01', '시험준비'
UNION ALL SELECT 'BBB', '002', '영어', 'C', '2000-06-06', '시험준비'
UNION ALL SELECT 'BBB', '002', '영어', 'D', '2000-08-08', '시험준비'
UNION ALL SELECT 'CCC', '003', '일어', 'A', '2000-01-02', '시험완료'
UNION ALL SELECT 'CCC', '003', '일어', 'B', '2000-01-03', '시험준비'
UNION ALL SELECT 'CCC', '003', '일어', 'C', '2000-01-04', '시험준비'
UNION ALL SELECT 'CCC', '003', '일어', 'D', '2000-01-05', '시험준비'
UNION ALL SELECT 'DDD', '003', '수학', 'A', '2000-04-29', '시험완료'
UNION ALL SELECT 'DDD', '003', '수학', 'B', '2000-05-04', '시험완료'
UNION ALL SELECT 'DDD', '003', '수학', 'C', '2000-05-20', '시험준비'
UNION ALL SELECT 'DDD', '003', '수학', 'D', '2000-05-30', '시험준비'
UNION ALL SELECT 'EEE', '003', '독어', 'A', '2000-01-29', '시험완료'
UNION ALL SELECT 'EEE', '003', '독어', 'B', '2000-02-01', '시험완료'
UNION ALL SELECT 'EEE', '003', '독어', 'C', '2000-02-08', '시험완료'
UNION ALL SELECT 'EEE', '003', '독어', 'D', '2000-02-28', '시험완료'
UNION ALL SELECT 'FFF', '002', '사회', 'A', '2000-02-02', '시험완료'
UNION ALL SELECT 'FFF', '002', '사회', 'B', '2000-04-01', '시험완료'
UNION ALL SELECT 'FFF', '002', '사회', 'C', '2000-06-06', '시험준비'
UNION ALL SELECT 'FFF', '002', '사회', 'D', '2000-08-08', '시험준비'
UNION ALL SELECT 'GGG', '002', '불어', 'A', '2000-04-08', '시험완료'
UNION ALL SELECT 'GGG', '002', '불어', 'B', '2000-05-09', '시험완료'
UNION ALL SELECT 'GGG', '002', '불어', 'C', '2000-06-06', '시험완료'
UNION ALL SELECT 'GGG', '002', '불어', 'D', '2000-08-08', '시험완료'
UNION ALL SELECT 'HHH', '002', '체육', 'A', '2000-02-02', '시험완료'
UNION ALL SELECT 'HHH', '002', '체육', 'B', '2000-04-01', '시험중'
UNION ALL SELECT 'HHH', '002', '체육', 'C', '2000-06-06', '시험준비'
UNION ALL SELECT 'HHH', '002', '체육', 'D', '2000-08-08', '시험준비'
)
SELECT 학교, 학번, 과목
, MIN(시험상태_A) 시험상태_A
, MIN(시험상태_B) 시험상태_B
, MIN(시험상태_C) 시험상태_C
, MIN(시험상태_D) 시험상태_D
, ISNULL(MAX(
CASE WHEN 시험상태 != '시험완료'
THEN CASE WHEN 시험종료일 <= '2000-04-04' THEN '지연'
WHEN 시험종료일 > '2000-04-04' THEN 시험상태
END
END
), '시험완료') AS 시험상태
FROM (
SELECT 학교, 학번, 과목
, CASE 시험방법 WHEN 'A' THEN 시험상태 END AS 시험상태_A
, CASE 시험방법 WHEN 'B' THEN 시험상태 END AS 시험상태_B
, CASE 시험방법 WHEN 'C' THEN 시험상태 END AS 시험상태_C
, CASE 시험방법 WHEN 'D' THEN 시험상태 END AS 시험상태_D
, 시험상태
, 시험방법
, 시험시작일
, ISNULL(
(SELECT TOP 1 b.시험시작일
FROM t b
WHERE b.학교 = a.학교
AND b.학번 = a.학번
AND b.과목 = a.과목
AND b.시험시작일 > a.시험시작일
ORDER BY 과목
), '9999-12-31') AS 시험종료일
FROM t a
) x
GROUP BY 학교, 학번, 과목
ORDER BY 학교, 학번, 과목
;
) 버튼을 클릭하여 작성 하시면 됩니다.