下面是我自己写的一个例子。困扰我半天才解决:现在把它分享给大家。不对的可以给我评论。共同进步
如下:
--db2数据库下
--任务是写四张表的关联关系:要素先是一张一张的关联,最后在连在一起。
--在连在一起的时候可能会出现问题:1、两张表在一起的时候,出现的结果是你想要的结果,但是当在连接第三张表的时候就会出现部分字段(你想要的字段)null空值。
--出现这个得从两方面考虑:一是:确实你关联出现问题,关联的字段不准确。二是:数据太少。
--解决之法就是你加条件判断下(具体如下)
1---没问题是自己想要的结果
select * from bdw_mrtg_inf s
left join (select gur_ctr_ar_id,gnt_ctr_tp_id from bdw_gur_ctr_ar) c
on s.mrtg_id=c.gur_ctr_ar_id
2---没问题是自己想要的结果
select * from bdw_mrtg_inf s
left join (select IDENTN_TP_ID,identn_no,open_ou_ip_id from bdw_idv) b
on s.input_ou_ip_id=b.open_ou_ip_id
3---没问题是自己想要的结果
select * from bdw_mrtg_inf s
left join (select cst_nm,ctr_nbr,cst_no from bdw_ln_ar_smy) a
on s.mrtg_id=a.cst_no;
一起--出现部分想要字段是null.
select * from bdw_mrtg_inf s
left join (select gur_ctr_ar_id,gnt_ctr_tp_id from bdw_gur_ctr_ar) c
on s.mrtg_id=c.gur_ctr_ar_id
left join (select IDENTN_TP_ID,identn_no,open_ou_ip_id from bdw_idv) b
on s.input_ou_ip_id=b.open_ou_ip_id
left join (select cst_nm,ctr_nbr,cst_no from bdw_ln_ar_smy) a
on s.mrtg_id=a.cst_no;
三个--出现部分想要字段是null.
select * from bdw_mrtg_inf s
left join (select gur_ctr_ar_id,gnt_ctr_tp_id from bdw_gur_ctr_ar) c
on s.mrtg_id=c.gur_ctr_ar_id
left join (select IDENTN_TP_ID,identn_no,open_ou_ip_id from bdw_idv) b
on s.input_ou_ip_id=b.open_ou_ip
这样就可以了判断自己写的是否正确了:
select * from bdw_mrtg_inf s
left join (select gur_ctr_ar_id,gnt_ctr_tp_id from bdw_gur_ctr_ar) c
on s.mrtg_id=c.gur_ctr_ar_id
left join (select IDENTN_TP_ID,identn_no,open_ou_ip_id from bdw_idv) b
on s.input_ou_ip_id=b.open_ou_ip_id
left join (select cst_nm,ctr_nbr,cst_no from bdw_ln_ar_smy) a
on s.mrtg_id=a.cst_no
where c.gur_ctr_ar_id is not null;