[JAVA] Lors de l'exécution d'une jointure externe complète sans utiliser de jointure externe complète
Si vous souhaitez effectuer une jointure externe complète, vous pouvez émettre une requête avec "jointure externe complète" comme mot clé. Souvent non pris en charge par certains serveurs de base de données.
Dans ce cas, utilisez "union all" pour effectuer une jointure externe complète. Un exemple est présenté dans la requête ci-dessous.
select distinct * from (select e1.seirino,e1.time,e2.buyer＿no,e2.buyer＿name from daityou e1 inner join a0160 e2 on e1.seirino = e2.seirino where e1.time ='2018-10-27 00:00:00.0') j1 left join (select distinct a1.seirino,a1.buyer＿no,a1.a0162＿no,a1.A0162＿NAME,a1.a0164＿no ,a1.a0164＿name,a2.a0168＿no ,a2.a0168＿name
from (select distinct q1.seirino,q1.buyer＿no,q1.a0162＿no,q1.A0162＿NAME,q2.a0164＿no ,q2.a0164＿name from A0162 q1 left join A0164 q2 on q1.seirino=q2.seirino and q1.buyer＿no=q2.buyer＿no and q1.a0162＿no=q2.a0164＿no union all select q2.seirino,q2.buyer＿no,null as a0164＿no,null as A0164＿NAME,q2.a0164＿no ,q2.a0164＿name from A0164 q2 where not exists(select * from A0162 q1 where q2.seiriNO = q1.seiriNO and q1.buyer＿no=q2.buyer＿no and q1.a0162＿no=q2.a0164＿no ) )
a1 gauche rejoindre A0168 a2 sur a1.seirino = a2.
seirino and a1.buyer＿no=a2.buyer＿no and (a1.a0162＿no=a2.a0168＿no or a1.a0164＿no=a2.a0168＿no)
union all
select d2.seirino,d2.buyer＿no,null as a0162＿no,null as A0162＿NAME,null as a0164＿no,null as A0164＿NAME,d2.a0168＿no ,d2.a0168＿name from A0168 d2 where not exists(select * from (select distinct c1.seirino,c1.buyer＿no,c1.a0162＿no,c1.A0162＿NAME,c2.a0164＿no ,c2.a0164＿name from A0162 c1 left join A0164 c2 on c1.seirino=c2.seirino and c1.buyer＿no=c2.buyer＿no and c1.a0162＿no=c2.a0164＿no union all select c2.seirino,c2.buyer＿no,null as a0164＿no,null as A0164＿NAME,c2.a0164＿no ,c2.a0164＿name from A0164 c2 where not exists (select * from A0162 c1 where c2.seiriNO = c1.seiriNO and c1.buyer＿no=c2.buyer＿no and c1.a0162＿no=c2.a0164＿no ) ) d1 where d2.seiriNO = d1.seiriNO and d1.buyer＿no=d2.buyer＿no and (d1.a0162＿no=d2.a0168＿no or d1.a0164＿no=d2.a0168＿no))) j2 on j1.seirino=j2.seirino and j1.buyer＿no=j2.buyer＿no
order by j1.seirino,j1.buyer＿no ,j2.a0162＿no ASC NULLS LAST,j2.a0164＿no ASC NULLS LAST,j2.a0168＿no ASC NULLS LAST;
◆ État de la table
SEIRINO TIME BUYER＿NO BUYER＿NAME SEIRINO BUYER＿NO A0162＿NO A0162＿NAME A0164＿NO A0164＿NAME A0168＿NO A0168＿NAME
1 2018-10-27 00:00:00.0 1 A 1 1 1 N1 1 A4 1 1
1 2018-10-27 00:00:00.0 1 A 1 1 2 N2 2 B4 2 2
1 2018-10-27 00:00:00.0 1 A 1 1 3 N3 3 C4 3 3
1 2018-10-27 00:00:00.0 1 A 1 1 4 N4 4 4 4 4
1 2018-10-27 00:00:00.0 1 A 1 1 null null 5 5 5 5
1 2018-10-27 00:00:00.0 1 A 1 1 null null null null 6 6
1 2018-10-27 00:00:00.0 2 B 1 2 1 M1 1 D4 1 1
1 2018-10-27 00:00:00.0 2 B 1 2 2 M2 2 D4 null null
1 2018-10-27 00:00:00.0 3 C 1 3 1 L1 1 D4 null null
1 2018-10-27 00:00:00.0 4 D null null null null null null null null
c'est tout.
Recommended Posts