sql六表联查

完成后是这样的

sql六表联查

select
case when (select  u.ui_name from user_info u where u.ui_delflag='0' and  rownum<2 and  u.ui_username=(select pr.pr_uid from  PORT_RESEARCH pr where pr.pr_dleflag='0' and  t.pri_foreign_key = pr.pr_fid)) is null then 
t.pri_portname else  (select  u.ui_name from user_info u where u.ui_delflag='0' and  rownum<2 and  u.ui_username=(select pr.pr_uid from  PORT_RESEARCH pr where pr.pr_dleflag='0' and  t.pri_foreign_key = pr.pr_fid))
 end,
--(select  u.ui_name from user_info u where u.ui_delflag='0' and  rownum<2 and  u.ui_username=(select pr.pr_uid from  PORT_RESEARCH pr where pr.pr_dleflag='0' and  t.pri_foreign_key = pr.pr_fid)),
--(select  u.ui_city from user_info u where u.ui_delflag='0' and  rownum<2 and u.ui_username=(select pr.pr_uid from  PORT_RESEARCH pr where  pr.pr_dleflag='0'and  t.pri_foreign_key = pr.pr_fid)),
case when (select  u.ui_city from user_info u where u.ui_delflag='0' and  rownum<2 and u.ui_username=(select pr.pr_uid from  PORT_RESEARCH pr where  pr.pr_dleflag='0'and  t.pri_foreign_key = pr.pr_fid)) is null then 
  (select ui.ui_city from user_info ui where ui.ui_name = t.pri_portname and ui.ui_delflag='0' and rownum<2)
  else (select  u.ui_city from user_info u where u.ui_delflag='0' and  rownum<2 and u.ui_username=(select pr.pr_uid from  PORT_RESEARCH pr where  pr.pr_dleflag='0'and  t.pri_foreign_key = pr.pr_fid))
  end ,
t.pri_portname,
(select b.b_name from BERTH b where b.b_delflag='0' and  t.pri_berthname=b.b_fid  and  b.b_wharf_id=(select pr.pr_uid from  PORT_RESEARCH pr where t.pri_foreign_key = pr.pr_fid)),
(select sd.sdd_dictionary_cname from sys_data_dictionary sd where sd.sdd_delfalg='0' and sd.sdd_dictionary_code=t.pri_commodity and sd.sdd_dictionary='0100'),
t.pri_machinery_category,t.pri_machinery_name,t.pri_power,t.pri_tonnage,t.pri_quantity,
(select sd.sdd_dictionary_cname from sys_data_dictionary sd where sd.sdd_delfalg='0' and sd.sdd_dictionary_code=t.pri_commodity and sd.sdd_dictionary='0200'),
(select sd.sdd_dictionary_cname from sys_data_dictionary sd where sd.sdd_delfalg='0' and sd.sdd_dictionary_code=t.pri_commodity and sd.sdd_dictionary='0300'),
(select sd.sdd_dictionary_cname from sys_data_dictionary sd where sd.sdd_delfalg='0' and sd.sdd_dictionary_code=t.pri_commodity and sd.sdd_dictionary='0400'),
(select sd.sdd_dictionary_cname from sys_data_dictionary sd where sd.sdd_delfalg='0' and sd.sdd_dictionary_code=t.pri_commodity and sd.sdd_dictionary='0500'),
t.pri_transform_time,t.pri_transform_quantity,t.pri_energy_after,
(case when (select te.name from temp te where te.name =t.pri_portname) is null then 1 else 0 end ),
(select ui_name from user_info where ui_delflag='0' and rownum < 2 and   ui_username = (select pr.pr_uid from  PORT_RESEARCH pr where t.pri_foreign_key = pr.pr_fid)),
(select ui_city from user_info where ui_delflag='0' and rownum < 2 and   ui_username = (select pr.pr_uid from  PORT_RESEARCH pr where t.pri_foreign_key = pr.pr_fid))
from PORT_RESEARCH_INFO t