Sql左连接包括空值 [英] Sql left join including nulls

问题描述

下面列出的代码用于从表中提取数据（weekly_data wd），但它也包含空值。我需要左连接，因为无论何时有数据都需要其他字段。如果有数据，我如何从每周数据中排除空值



我尝试过：

 选择 sm.sugar_mill_name，mbz.mill，fhi.hczname  as  zone，sd.name  as  station，
 round（ coalesce （wd.dvalue， 0 ）， 1 ） as  Station_Reading 
 来自 sugar_mills sm  join  
 mill_by_zone mbz 
   sm.sugar_mill_name = mbz.mill  join  
 fca_hcz_info fhi 
 < span class =code-keyword> on  fhi.hcz = mbz.zone  join  
 zone_by_station zbs 
 >  zbs.zone = fhi.hcz  join  
 station_details sd 
  on  sd.station_num = zbs。 station  left   join  
 weekly_data wd 
  on  wd.station_num = sd.station_num 和 
 wd.station_num = zbs.station 和 
 wd.record_year =  2019  和 
 wd.record_week =  19  和 
 wd.dcode =  1 ; 

解决方案

LEFT JOIN - 按设计 - 将为所有失败的连接带来NULL ...所以如果你正在寻找如果联接表中的值不存在，则不会排除原始行，而是用NULL填充空白...

使用INNER JOIN - 它将消除所有在连接表中没有连接行的原始行...

http://www.sql-join.com/sql-join-types [ ^ ]

我不完全确定，我现在无法测试，但是我想在最后（左）联接中你引入的元素似乎属于 JOIN 中的 WHERE 子句声明。

也许试试：

 选择 sm.sugar_mill_name，mbz。 mill，fhi.hczname  as  zone，sd.name  as  station，
 round（ coalesce （wd.dvalue， 0 ）， 1  ） as  Station_Reading 
 来自m  sugar_mills sm  join  
 mill_by_zone mbz 
  on  sm.sugar_mill_name = mbz.mill  join  
 fca_hcz_info fhi 
  on  fhi.hcz = mbz。 zone  join  
 zone_by_station zbs 
  on  zbs.zone = fhi.hcz  join  
 station_details sd 
  on  sd.station_num = zbs.station  left   join  
 weekly_data wd 
  on  wd.station_num = sd.station_num 
 其中 wd.record_year =  2019  和 
 wd.record_week =  19  和 
 wd.dcode =  1 ; 

此外， wd.station_num 已加入 sd.station_num ，它本身已加入 zbs.station ;没有必要考虑 wd.station_num = zbs.station 。



希望这有帮助。

The following code listed below is used to bring data from the table (weekly_data wd) but it also includes nulls. I need the left join due to needing the other fields irrespective when there is data or not. In the event if there is data how would I exclude null values from weekly data

What I have tried:

select sm.sugar_mill_name, mbz.mill, fhi.hczname as zone, sd.name as station, 
    round(coalesce(wd.dvalue, 0), 1) as Station_Reading
from sugar_mills sm join
     mill_by_zone mbz
     on sm.sugar_mill_name = mbz.mill join
     fca_hcz_info fhi
     on fhi.hcz = mbz.zone join 
     zone_by_station zbs
     on zbs.zone = fhi.hcz join 
     station_details sd
     on sd.station_num = zbs.station left join 
     weekly_data wd
     on wd.station_num = sd.station_num and
        wd.station_num = zbs.station and 
        wd.record_year = 2019 and
        wd.record_week = 19 and
        wd.dcode = 1;

解决方案

LEFT JOIN - by design - will bring NULLs for all the failed joins... So if you are looking up a value in the joined table that does not exists it will not exclude the original row but fill the gaps with NULLs...
Use INNER JOIN - it will eliminate all the original rows, that has no joined rows in the joined table...
http://www.sql-join.com/sql-join-types[^]

I'm not entirely sure, and I cannot test it right now, but I think on the last (left) join you are introducing elements which seem to belong to a WHERE clause in a JOIN statement.
Maybe try:

select sm.sugar_mill_name, mbz.mill, fhi.hczname as zone, sd.name as station, 
    round(coalesce(wd.dvalue, 0), 1) as Station_Reading
from sugar_mills sm join
     mill_by_zone mbz
     on sm.sugar_mill_name = mbz.mill join
     fca_hcz_info fhi
     on fhi.hcz = mbz.zone join 
     zone_by_station zbs
     on zbs.zone = fhi.hcz join 
     station_details sd
     on sd.station_num = zbs.station left join 
     weekly_data wd
     on wd.station_num = sd.station_num
where wd.record_year = 2019 and
      wd.record_week = 19 and
      wd.dcode = 1;

Moreover, wd.station_num is already joined to sd.station_num, which itself is joined to zbs.station; there is no need to account for wd.station_num = zbs.station.

Hope this helps.

这篇关于Sql左连接包括空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！