데이터베이스 질문과 답변
첨부한 jpg 의 상단 두개를 가지고 join 하여
아래의 결과값을 원하고 있습니다
A, B 테이블에서 A 날짜보다 작거나 큰값의 MAX 를 가져오면 될것 같은데
잘안되고 있습니다.
도움을좀 주세요 ^^
by 마농
[2009.09.15 08:13:41]
WITH t1 AS
(
SELECT '20090831' std_date, 'KCD' id FROM dual
UNION ALL SELECT '20090930', 'KCD' FROM dual
UNION ALL SELECT '20091031', 'KCD' FROM dual
UNION ALL SELECT '20091130', 'KCD' FROM dual
UNION ALL SELECT '20091231', 'KCD' FROM dual
)
, t2 AS
(
SELECT 'KCD' id, '20090828' std_date, 10 amt FROM dual
UNION ALL SELECT 'KCD', '20091005', 11 FROM dual
UNION ALL SELECT 'KCD', '20100826', 12 FROM dual
UNION ALL SELECT 'KCD', '20110101', 13 FROM dual
)
SELECT t1.std_date, t1.id
, MIN(amt) KEEP(DENSE_RANK LAST ORDER BY t2.std_date) amt
FROM t1, t2
WHERE t1.id = t2.id
AND t1.std_date >= t2.std_date
GROUP BY t1.std_date, t1.id
;
(
SELECT '20090831' std_date, 'KCD' id FROM dual
UNION ALL SELECT '20090930', 'KCD' FROM dual
UNION ALL SELECT '20091031', 'KCD' FROM dual
UNION ALL SELECT '20091130', 'KCD' FROM dual
UNION ALL SELECT '20091231', 'KCD' FROM dual
)
, t2 AS
(
SELECT 'KCD' id, '20090828' std_date, 10 amt FROM dual
UNION ALL SELECT 'KCD', '20091005', 11 FROM dual
UNION ALL SELECT 'KCD', '20100826', 12 FROM dual
UNION ALL SELECT 'KCD', '20110101', 13 FROM dual
)
SELECT t1.std_date, t1.id
, MIN(amt) KEEP(DENSE_RANK LAST ORDER BY t2.std_date) amt
FROM t1, t2
WHERE t1.id = t2.id
AND t1.std_date >= t2.std_date
GROUP BY t1.std_date, t1.id
;
by karl
[2009.09.15 09:50:24]
마농님 감사합니다. 함수공부좀 해야겠네요^^ 잘 활용할께요.
by 서성우
[2009.09.16 09:00:35]
WITH test AS
(
SELECT '20090831' s_date , 'KCD' id FROM dual UNION ALL
SELECT '20090930' , 'KCD' FROM dual UNION ALL
SELECT '20091031' , 'KCD' FROM dual UNION ALL
SELECT '20091130' , 'KCD' FROM dual UNION ALL
SELECT '20091231' , 'KCD' FROM dual
)
, test2 AS
(
SELECT 'KCD' id , '20090828' s_date , 10 amt FROM dual UNION ALL
SELECT 'KCD' , '20091005' , 11 FROM dual UNION ALL
SELECT 'KCD' , '20100826' , 12 FROM dual UNION ALL
SELECT 'KCD' , '20110101' , 13 FROM dual
)
SELECT a.s_date , a.id , Max(b.amt)
FROM test a, test2 b
WHERE a.id = b.id
AND a.s_date >= b.s_date
GROUP BY a.s_date , a.id
(
SELECT '20090831' s_date , 'KCD' id FROM dual UNION ALL
SELECT '20090930' , 'KCD' FROM dual UNION ALL
SELECT '20091031' , 'KCD' FROM dual UNION ALL
SELECT '20091130' , 'KCD' FROM dual UNION ALL
SELECT '20091231' , 'KCD' FROM dual
)
, test2 AS
(
SELECT 'KCD' id , '20090828' s_date , 10 amt FROM dual UNION ALL
SELECT 'KCD' , '20091005' , 11 FROM dual UNION ALL
SELECT 'KCD' , '20100826' , 12 FROM dual UNION ALL
SELECT 'KCD' , '20110101' , 13 FROM dual
)
SELECT a.s_date , a.id , Max(b.amt)
FROM test a, test2 b
WHERE a.id = b.id
AND a.s_date >= b.s_date
GROUP BY a.s_date , a.id
by karl
[2009.09.16 14:48:40]
서성우님 감사합니다.. 저렇게 했었는데 안되서 질문했던건데.. 하핫... ^^;;
by 마농
[2009.09.21 09:41:40]
서성우님의 방법은 위의 예시와 같이 날짜가 커짐에 따라서 값도 커질 경우에만 올바른 자료가 조회됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.