데이터베이스 질문과 답변

마농님 밑에 질문에 답변감사합니다. 근데 표준ANSI 로 어떻게 해야하는지요 ? 0 2 3,675

by 열심히 [SQL Query] LEAD 분석함수 [2012.03.02 15:27:37]

마농님 밑에 질문에 대한 답변 감사드립니다.
근데 오라클이 아닌 MSSQL 이라  LEAD 나 오라클 고유 펑션이 안됩니다.
표준 ANSI SQL 로는 어떻게 할 수 있는지요 ?


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 학교, 학번, 과목
;
by 마농 [2012.03.02 16:34:33]

아래 글에 MS-SQL 용으로 바꿔서 답글 달았습니다.
by 열심히 [2012.03.02 17:25:17]
항상 좋은 답변 감사드립니다. 많이 배웁니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입