데이터베이스 질문과 답변
by 커피향 [2014.08.05 11:56:42]
WITH t AS
(
SELECT '20140101' dtseq, 0 seq from dual
UNION ALL SELECT '20140101', 1 FROM dual
UNION ALL SELECT '20140101', 2 FROM dual)
select * from t
WITH s AS
(
SELECT '001' sa, '20140101' dtseq, 0 seq from dual
UNION ALL SELECT '001', '20140101', 1 FROM dual
UNION ALL SELECT '002', '20140101', 1 FROM dual
UNION ALL SELECT '002', '20140101', 2 FROM dual
)
select * from s
WITH r AS
(
SELECT '001' sa, '20140101' dtseq, 0 seq from dual
UNION ALL SELECT '001', '20140101', 1 FROM dual
UNION ALL SELECT '001', '20140101', 2 FROM dual
UNION ALL SELECT '002', '20140101', 0 FROM dual
UNION ALL SELECT '002', '20140101', 1 FROM dual
UNION ALL SELECT '002', '20140101', 2 FROM dual
)
t와 s를 조인해서 r 처럼 나오게 하려면 어떻게 해야 하나요.
답변 부탁드립니다.
WITH T AS ( SELECT '20140101' dtseq, 0 seq from dual UNION ALL SELECT '20140101', 1 FROM dual UNION ALL SELECT '20140101', 2 FROM dual ), S AS ( SELECT '001' sa, '20140101' dtseq, 0 seq from dual UNION ALL SELECT '001', '20140101', 1 FROM dual UNION ALL SELECT '002', '20140101', 1 FROM dual UNION ALL SELECT '002', '20140101', 2 FROM dual ) SELECT sa, dtseq, seq FROM T CROSS JOIN (SELECT DISTINCT sa FROM S) ORDER BY sa, seq ;
SELECT s.sa, t.dtseq, t.seq FROM t LEFT OUTER JOIN s PARTITION BY (s.sa) ON t.dtseq = s.dtseq AND t.seq = s.seq ;
SELECT s.sa, t.dtseq, t.seq FROM t LEFT OUTER JOIN s PARTITION BY (s.sa) ON t.dtseq = s.dtseq AND t.seq = s.seq AND T.SEQ = 2 <---- 이 조건으로는 검색이 안될까요? ;결과가 아래와 같이 나오도록 하고 싶은경우 (
SELECT '001', '20140101', 2 FROM dual
UNION ALL SELECT '002', '20140101', 2 FROM dual
)
select * from 검색
ON t.dtseq = s.dtseq
AND t.seq = s.seq
WHERE T.SEQ = 2
자문자답
SELECT s.sa, t.dtseq, t.seq FROM t LEFT OUTER JOIN s PARTITION BY (s.sa) ON (t.dtseq = s.dtseq AND t.seq = s.seq )WHERE T.SEQ = 2 ;감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.