데이터베이스 퀴즈 및 튜닝 문제
[퀴즈] 특정 기간중에 진행중인 행사를 조회하세요.
행사 코드와 행사 진행기간(시작일, 종료일)을 가진 다음과 같은 행사 테이블이 있습니다.
이 테이블을 이용하여 특정 조회 기간중에 진행중인 또는 진행중이었던 행사를 조회하세요.
진행기간이 조회기간에 단 하루라도 포함되는 행사는 조회되어야 합니다.
조회 조건 : '20091115' ~ '20091121'
WITH t AS
(
SELECT '행사1' cd, '20091101' sdt, '20091107' edt FROM dual
UNION ALL SELECT '행사2', '20091109', '20091115' FROM dual
UNION ALL SELECT '행사3', '20091113', '20091117' FROM dual
UNION ALL SELECT '행사4', '20091115', '20091121' FROM dual
UNION ALL SELECT '행사5', '20091117', '20091118' FROM dual
UNION ALL SELECT '행사6', '20091120', '20091123' FROM dual
UNION ALL SELECT '행사7', '20091122', '20091130' FROM dual
UNION ALL SELECT '행사8', '20091101', '20091130' FROM dual
)
SELECT * FROM t;
cd sdt edt cd sdt edt 행사1 20091101 20091107 행사2 20091109 20091115 행사2 20091109 20091115 행사3 20091113 20091117 행사3 20091113 20091117 행사4 20091115 20091121 행사4 20091115 20091121 행사5 20091117 20091118 행사5 20091117 20091118 행사6 20091120 20091123 행사6 20091120 20091123 행사8 20091101 20091130 행사7 20091122 20091130 행사8 20091101 20091130
원본
결과
[정답보기] <=== 트리플클릭
SELECT * FROM t
WHERE edt >= '20091115'
AND sdt <= '20091121'
;
select *
from t
where edt >= '20091115'
and sdt <= '20091121'
화면단에서 조회기간이 주어지면 그 기간동안 행사가 걸쳐있는 모든 날짜를 구하시오
EX)조회기간이 '20091115' ~ '20091121' 로 주어지면 행사3은 15,16,17일 3일이 걸쳐있습니다.
WITH t AS
(
SELECT '행사1' cd, '20091101' sdt, '20091107' edt FROM dual
UNION ALL SELECT '행사2', '20091109', '20091115' FROM dual
UNION ALL SELECT '행사3', '20091113', '20091117' FROM dual
UNION ALL SELECT '행사4', '20091115', '20091121' FROM dual
UNION ALL SELECT '행사5', '20091117', '20091118' FROM dual
UNION ALL SELECT '행사6', '20091120', '20091123' FROM dual
UNION ALL SELECT '행사7', '20091122', '20091130' FROM dual
UNION ALL SELECT '행사8', '20091101', '20091130' FROM dual
)
, COPY AS(
SELECT TO_DATE('20091115')+(LEVEL-1) BDATE
FROM DUAL
CONNECT BY LEVEL<=(TO_DATE('20091121')- TO_DATE('20091115')+1)
)
SELECT CD
,SDT
,EDT
,BDATE
FROM t A,COPY B
WHERE B.BDATE BETWEEN TO_DATE(SDT) AND TO_DATE(EDT)
ORDER BY CD,BDATE
(
SELECT '행사1' cd, '20091101' sdt, '20091107' edt FROM dual
UNION ALL SELECT '행사2', '20091109', '20091115' FROM dual
UNION ALL SELECT '행사3', '20091113', '20091117' FROM dual
UNION ALL SELECT '행사4', '20091115', '20091121' FROM dual
UNION ALL SELECT '행사5', '20091117', '20091118' FROM dual
UNION ALL SELECT '행사6', '20091120', '20091123' FROM dual
UNION ALL SELECT '행사7', '20091122', '20091130' FROM dual
UNION ALL SELECT '행사8', '20091101', '20091130' FROM dual
)
SELECT CD,SDT,EDT
FROM t
WHERE '20091115' <= SDT
AND EDT <= '20091121'
;
) 버튼을 클릭하여 작성 하시면 됩니다.