SQL：选择具有空值的其他列

赞纳比

我目前在数据库中没有本周的数据，因为它没有结束。数据在下一周的星期一输入。因此查询未显示任何结果。查询是使用连接创建的。

select 
    sm.sugar_mill_name, mbz.mill, fhi.hczname as zone, sd.name as station, 
    round(nvl(wd.dvalue, 0), 1) as Station_Reading
from 
    mill_by_zone mbz
join 
    sugar_mills sm 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
join 
    weekly_data wd on wd.station_num = sd.station_num and wd.station_num = zbs.station
where 
    wd.record_year = 2019 
    and wd.record_week = 19 
    and wd.dcode = 1

我如何显示常量数据，即 sm.sugar_mill_name, mbz.mill, fhi.hczname as zone, sd.name as station

round(nvl(wd.dvalue,0),1) 由于 Station_Reading 当前为空白，因为尚未输入任何数据。

戈登·利诺夫

您将使用LEFT JOINs 引入每周数据。这样做时，还需要将列上的过滤条件移动到ON子句中：

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。