本文介绍了Sql左连接包括空值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 下面列出的代码用于从表中提取数据(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 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 dataWhat 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_Readingfrom 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_Readingfrom 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_numwhere 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左连接包括空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
09-05 16:06
查看更多