합집합, 2개 테이블을 1개 테이블로 합치기

합집합 (UNION)

2개 테이블을 1개 테이블로 합친다.

UNION - 중복제거
UNION ALL - 중복허용

use stock;
SELECT 'VI_' as trno, shcode, 
CONCAT(vi_gubun,' ',svi_recprice,' ',dvi_recprice,' ',vi_trgprice,' ',shcode,' ',ref_shcode,' ',time1) as data, 
CONCAT(ddate,' ',ttime) as ttime FROM tblvi
UNION
SELECT 'JIF' as trno, '000000' as shcode, concat(jangubun,' ', jstatus) as data, concat(ddate,' ', ttime) as ttime FROM tbljif
order by ttime;

 

select count(*)
from (
SELECT trno, shcode, 
CONCAT(
trno,' ',hotime,' ',yeprice,' ',yevolume,' ',jnilysign,' ',preychange,' ',jnilydrate,' ',yofferho0,' ',
ybidho0,' ',yofferrem0,' ',ybidrem0,' ',shcode,' '  
) AS data,
CONCAT(ddate,' ',ttime) as ttime
FROM tblyk3_ys3
 
UNION
 
SELECT 'VI_' as trno, shcode, 
CONCAT(vi_gubun,' ',svi_recprice,' ',dvi_recprice,' ',vi_trgprice,' ',shcode,' ',ref_shcode,' ',time1) as data, 
CONCAT(ddate,' ',ttime) as ttime 
FROM tblvi
 
UNION
 
SELECT trno, shcode, 
CONCAT(
hotime,' ',
offerho1,' ',bidho1,' ',offerrem1,' ',bidrem1,' ',offerho2,' ',bidho2,' ',offerrem2,' ',bidrem2,' ',
offerho3,' ',bidho3,' ',offerrem3,' ',bidrem3,' ',offerho4,' ',bidho4,' ',offerrem4,' ',bidrem4,' ',
offerho5,' ',bidho5,' ',offerrem5,' ',bidrem5,' ',offerho6,' ',bidho6,' ',offerrem6,' ',bidrem6,' ',
offerho7,' ',bidho7,' ',offerrem7,' ',bidrem7,' ',offerho8,' ',bidho8,' ',offerrem8,' ',bidrem8,' ',
offerho9,' ',bidho9,' ',offerrem9,' ',bidrem9,' ',offerho10,' ',bidho10,' ',offerrem10,' ',bidrem10,' ',
totofferrem,' ',totbidrem,' ',donsigubun,' ',shcode,' ',alloc_gubun
) AS data,
CONCAT(ddate,' ',ttime) as ttime
FROM stock.tblha_h1
 
UNION
 
SELECT trno, shcode,
CONCAT(
chetime,' ',sign,' ',change1,' ',drate,' ',price,' ',opentime,' ',open,' ',hightime,' ',high,' ',lowtime,' ',
low,' ',cgubun,' ',cvolume,' ',volume,' ',value1,' ',mdvolume,' ',mdchecnt,' ',msvolume,' ',mschecnt,' ',
cpower,' ',w_avrg,' ',offerho,' ',bidho,' ',status,' ',jnilvolume,' ',shcode,' '
) AS data,
CONCAT(ddate,' ',ttime) as ttime
FROM stock.tblk3_s3
ORDER BY ttime
) as b; /* 별명 지정해야 함 */

SQL 실행 결과>

- 위 SQL코드에서 CONCAT()을 CONCAT_WS(' ', 컬럼1, 컬럼2....) 로 바꾸면 코드 간단해진다.
(http://petra.tistory.com/1017 참고)