多个left join 产生多个结果

最新推荐文章于 2024-07-29 04:03:02 发布

松门一枝花

最新推荐文章于 2024-07-29 04:03:02 发布

阅读量3.7k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/zengmingen/article/details/51063985

版权

数据库专栏收录该内容

98 篇文章 2 订阅

订阅专栏

select 
		  a.*,
		  to_char(To_date(20160403000000, 'yyyyMMddhh24miss'),'yyyy/mm/dd') as omc_start_time,
		  to_char(To_date(20160404000000, 'yyyyMMddhh24miss'),'yyyy/mm/dd') as omc_end_time,
		  ROUND(sc."切换成功率",2) AS "OMC-源小区切换成功率%",
		  ROUND(sc."同频切换成功率",2) AS "OMC-源小区同频切换成功率%",
		  ROUND(sc."异频切换成功率",2) AS "OMC-源小区异频切换成功率%",
		  ROUND(sc."切换失败次数(含准备)",2) AS "OMC-源小区切换失败次数含准备",
		  sb.alarm as s_alarm,
		  ROUND(tc."切换成功率",2) AS "OMC-目标小区切换成功率%",
		  ROUND(tc."同频切换成功率",2) AS "OMC-目标小区同频切换成功率%",
		  ROUND(tc."异频切换成功率",2) AS "OMC-目标小区异频切换成功率%",
		  ROUND(tc."切换失败次数(含准备)",2) AS "OMC-目标小区切换失败次数含准备",
		  tb.alarm as t_alarm
		from (
		  select x.*,y.cellname scell_name,z.cellname tcell_name,to_number(y.lat) slat,to_number(y.lon) slon,to_number(z.lat) tlat,to_number(z.lon) tlon
		  from
		  (select ci_s scell,cellname_s,band_s,lon_s ,lat_s ,ci_d tcell,cellname_d,band_d,lon_d,lat_d,cnt,cnt_total,pingpong_ratio from TMP_TBX_100_0_A6) x
		  inner join
		  TMP_TBX_100_0_CFG1 y on x.scell=y.ci
		  inner join
		  TMP_TBX_100_0_CFG1 z on x.tcell=z.ci
		) a
		left join TMP_TBX_100_0_OS3 sb on to_char(round(substr(a.scell,-9)/256,0))=sb.ENBID
		left join TMP_TBX_100_0_OS4 sc on to_number(substr(a.scell,-9))=to_number(sc.ECGI)
		left join TMP_TBX_100_0_OS3 tb on to_char(round(substr(a.tcell,-9)/256,0))=tb.ENBID
		left join TMP_TBX_100_0_OS4 tc on to_number(substr(a.tcell,-9))=to_number(tc.ECGI)

最后的四个 left join 因为 on的 “=” 号前后条件不同，产生了4条相同的记录。

除非主键关联，才会是左表的条数。