1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| insert into TBL_TMP (EID0, NUM0, FID0, TP0, RVD0, TSI0, EID1, NUM1, FID1, TP1, RVD1, TSI1
select * from (select distinct
t6.ID as EID, t4.NUM, t1.ID, t3.TP, t1.RVD, t2.TSI
from
(select distinct
t6.ID as EID, t4.NUM, t1.ID, t1.SMI, t1.RVD, t2.TSI, t3.TP, t3.TVD, t2.FID
from TABLE1 t1
inner join TABLE2 t2 on t1.SMI = t2.ID
inner join TABLE3 t3 on t1.RVD = t3.ID
inner join TABLE4 t4 on t2.FID = t4.ID
inner join TABLE5 t5 on t4.SSI = t5.ID
left join TABLE6 t6 on t6.ID = t5.EID
where t2.TSI = 0 and t6.ID = 5000
) a
full join
(select distinct
t6.ID as EID, t4.NUM, t1.ID, t1.SMI, t1.RVD, t2.TSI, t3.TP, t3.TVD, t2.FID
from TABLE1 t1
inner join TABLE2 t2 on t1.SMI = t2.ID
inner join TABLE3 t3 on t1.RVD = t3.ID
inner join TABLE4 t4 on t2.FID = t4.ID
inner join TABLE5 t5 on t4.SSI = t5.ID
left join TABLE6 t6 on t6.ID = t5.EID
where t2.TSI = 1 and t6.ID = 5000
and
) b
on a.EID = b.EID and a.NUM = b.NUM and a.TP = b.TP
order by 2,4 |
Partager