데이터베이스 질문과 답변
이런 식으로 작성중인데 잘안되네요... 도와주시면 많은 공부가 될것 같습니다.
SELECT a.a_pk1,
a.a_pk2,
b.b_pk,
b.b_year,
c.c_pk,
c.c_year,
d.d_pk,
d.d_year
FROM ta_master a LEFT OUTER JOIN ta_year1 b
ON ( b.a_pk1 = a.a_pk1
AND b.a_pk2 = a.a_pk2
)
LEFT OUTER JOIN ta_year2 c
ON ( c.a_pk1 = a.a_pk1
AND c.a_pk2 = a.a_pk2
)
LEFT OUTER JOIN ta_year1 d
ON ( d.a_pk1 = a.a_pk1
AND d.a_pk2 = a.a_pk2
)
WHERE a.a_pk1 = '12'
AND a.a_pk2 = '16771'
;
[원하는 결과]
------------------------------------------------------------------------
a_pk1 성명 년도 b_pk c_pk d_pk
------------------------------------------------------------------------
00 홍길동 2014 111 - 333
00 홍길동 2015 - - 301
00 홍길동 2016 112 222 303
-------------------------------------------------------------------------
[테이블구조]
SELECT a.a_pk1 , a.a_pk2 , a.성명 , b.year , b.b_pk , b.c_pk , b.d_pk FROM ta_master a INNER JOIN (SELECT a_pk1, a_pk2, year , MIN(b_pk) b_pk , MIN(c_pk) c_pk , MIN(d_pk) d_pk FROM (SELECT a_pk1, a_pk2 , b_year year , b_pk b_pk , NULL c_pk , NULL d_pk FROM ta_year1 WHERE a_pk1 = '12' AND a_pk2 = '16771' UNION ALL SELECT a_pk1, a_pk2 , c_year year , NULL b_pk , c_pk c_pk , NULL d_pk FROM ta_year2 WHERE a_pk1 = '12' AND a_pk2 = '16771' UNION ALL SELECT a_pk1, a_pk2 , d_year year , NULL b_pk , NULL c_pk , d_pk d_pk FROM ta_year3 WHERE a_pk1 = '12' AND a_pk2 = '16771' ) a GROUP BY a_pk1, a_pk2, year ) b ON a.a_pk1 = b.a_pk1 AND a.a_pk2 = b.a_pk2 ;
쿼리좀 설명해주실분 있나요? 혼자 보면서 공부중인데 어렵네요 ㅜㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.