將3個union的資料合併成一列

 

select a,b,c from x

union

select a,b,d from y

union

select a,b,e from z

得到資料為15000筆的a,b,c

欲合併得到資料為-->a,b,c,d,e  (大約5500筆資料)

以下執行時間為20秒-->

select s1.SAL_MST_PERSONALID ID,s1.SAL_MST_ID,t1.TranDate td1,t2.TranDate td2,t3.TranDate td3  from (select * from sal001 where sal_mst_lay_off = 0) s1 

left join (

SELECT s1.SAL_MST_PERSONALID ID,t9.FK_SAL_MST_ID,DATE_FORMAT(t9.trs_tcs_apply_date, '%Y%m%d') TranDate FROM trs009 t9  inner join sal001 s1 on s1.sal_mst_id=t9.fk_sal_mst_id

WHERE t9.FK_TRS_MST_ID IN 

(SELECT  t1.TRS_MST_ID FROM trs001 t1 WHERE t1.TRS_MST_TYPE_OLDER=1

AND t1.TRS_MST_CLASS_RELEASE=1

AND DATE_FORMAT(t1.TRS_MST_QUIZ_STARTDATE, '%Y') = DATE_FORMAT(CURDATE(), '%Y')-1)

AND t9.TRS_TCS_IS_PASS = true

) t1 on s1.SAL_MST_ID=t1.FK_SAL_MST_ID

left join (

SELECT s1.SAL_MST_PERSONALID ID,t9.FK_SAL_MST_ID,DATE_FORMAT(max(t9.trs_tcs_apply_date), '%Y%m%d') TranDate FROM trs009 t9  inner join sal001 s1 on s1.sal_mst_id=t9.fk_sal_mst_id

WHERE t9.FK_TRS_MST_ID IN 

(SELECT  t1.TRS_MST_ID FROM trs001 t1 WHERE t1.trs_mst_name like '%XXXXXX%'   AND t1.TRS_MST_CLASS_RELEASE=1)

AND t9.TRS_TCS_IS_PASS = true AND s1.sal_mst_lay_off = 0

group by s1.SAL_MST_PERSONALID,t9.FK_SAL_MST_ID

) t2 on s1.SAL_MST_ID=t2.FK_SAL_MST_ID

left join (

SELECT s1.SAL_MST_PERSONALID ID,t9.FK_SAL_MST_ID,DATE_FORMAT(max(t9.trs_tcs_apply_date), '%Y%m%d') TranDate FROM trs009 t9  inner join sal001 s1 on s1.sal_mst_id=t9.fk_sal_mst_id

WHERE t9.FK_TRS_MST_ID IN 

(SELECT  t1.TRS_MST_ID FROM trs001 t1 WHERE t1.trs_mst_name like '%XXXXXX%' AND t1.TRS_MST_CLASS_RELEASE=1)

AND t9.TRS_TCS_IS_PASS = true  AND s1.sal_mst_lay_off = 0

group by s1.SAL_MST_PERSONALID,t9.FK_SAL_MST_ID

) t3 on s1.SAL_MST_ID=t3.FK_SAL_MST_ID;

使用以下SQL加速資料取得(2個2個JOIN),時間大幅下降至1秒

select  a1.SAL_MST_PERSONALID,a1.FK_SAL_MST_ID,a1.td1,a1.td2,a2.td3 from

(select  x1.SAL_MST_PERSONALID,x1.FK_SAL_MST_ID,x1.td1,x2.td2 from

(SELECT s1.SAL_MST_PERSONALID ,t9.FK_SAL_MST_ID,DATE_FORMAT(t9.trs_tcs_apply_date, '%Y%m%d') td1  

FROM trs009 t9

inner join sal001 s1 on s1.sal_mst_id=t9.fk_sal_mst_id

WHERE s1.sal_mst_lay_off = 0 and t9.FK_TRS_MST_ID IN 

(SELECT  t1.TRS_MST_ID FROM trs001 t1 WHERE t1.TRS_MST_TYPE_OLDER=1

AND t1.TRS_MST_CLASS_RELEASE=1

AND DATE_FORMAT(t1.TRS_MST_QUIZ_STARTDATE, '%Y') = DATE_FORMAT(CURDATE(), '%Y')-1)

AND t9.TRS_TCS_IS_PASS = true) x1

left join 

(SELECT s1.SAL_MST_PERSONALID ,t9.FK_SAL_MST_ID,DATE_FORMAT(max(t9.trs_tcs_apply_date), '%Y%m%d') td2 FROM trs009 t9

inner join sal001 s1 on s1.sal_mst_id=t9.fk_sal_mst_id

WHERE t9.FK_TRS_MST_ID IN 

(SELECT  t1.TRS_MST_ID FROM trs001 t1 WHERE t1.trs_mst_name like '%XXXXXX%'

AND t1.TRS_MST_CLASS_RELEASE=1)

AND t9.TRS_TCS_IS_PASS = true AND s1.sal_mst_lay_off = 0

group by s1.SAL_MST_PERSONALID,t9.FK_SAL_MST_ID) x2 

                                on x1.SAL_MST_PERSONALID=x2.SAL_MST_PERSONALID and x1.FK_SAL_MST_ID=x2.FK_SAL_MST_ID) a1

left join

(SELECT s1.SAL_MST_PERSONALID,t9.FK_SAL_MST_ID,DATE_FORMAT(max(t9.trs_tcs_apply_date), '%Y%m%d') td3 FROM trs009 t9

inner join sal001 s1 on s1.sal_mst_id=t9.fk_sal_mst_id

WHERE t9.FK_TRS_MST_ID IN 

(SELECT  t1.TRS_MST_ID FROM trs001 t1 WHERE t1.trs_mst_name like '%XXXXXXX%'

AND t1.TRS_MST_CLASS_RELEASE=1)

AND t9.TRS_TCS_IS_PASS = true  AND s1.sal_mst_lay_off = 0

group by s1.SAL_MST_PERSONALID,t9.FK_SAL_MST_ID) a2 

                                on a1.SAL_MST_PERSONALID=a2.SAL_MST_PERSONALID and a1.FK_SAL_MST_ID=a2.FK_SAL_MST_ID;