SQL：使用空值获取平均值

debugcn 发表于 Dev

弗莱明普特

我对以下查询有疑问。它没有向我显示具有无等级（零值）的配对的葡萄酒，我需要显示所有葡萄酒，甚至没有AVG等级的葡萄酒。我怎样才能做到这一点？

SELECT wineid
     , wine_name
     , winetype_name
     , wine_img
     , producer_name
     , region_name
     , sponsored
     , recipe_name
     , recipeid
     , grade_name
     , nota
  from
     ( select wine.id as wineid
            , wine_name
            , winetype_name
            , wine_img
            , producer.producer_name AS producer_name
            , region_name
            , sponsored
            , recipe_name     
            , recipe.id as recipeid
            , **round(avg(grade_id),0) as nota**
        from wine
            , producer          
            , winetype
            , region
            , recipe
            , rating
            , pairing
        where wine.id = pairing.wine_id 
         and wine.winetype_id = winetype.id 
         and wine.producer_id = producer.id 
         and wine.region_id = region.id 
         and recipe.id = pairing.recipe_id 
         and rating.pairing_id = pairing.id 
        group 
            by wineid
             , wine_name
             , recipe_name) as temp
     , grade
 where grade.id = temp.nota 
  order 
    by sponsored desc
     , nota desc;

专线小巴

不要使用隐式连接（在from子句中使用逗号）！而是使用现代的显式联接（带有on关键字）。它们不仅是事实上的标准，而且很容易将它们更改为left join，这似乎是您在这里需要的。

这是使用现代联接语法的查询的更新版本；我将一些联接更改为左联接（您可能需要进行更多调整，因为在没有看到实际样本数据的情况下，几乎不可能猜测应该准确更改哪些联接）：

select
    wineid, 
    wine_name, 
    winetype_name, 
    wine_img, 
    producer_name, 
    region_name, 
    sponsored, 
    recipe_name, 
    recipeid, 
    grade_name, 
    nota
from 
    (
        select 
            wine.id as wineid, 
            wine_name, 
            winetype_name, 
            wine_img, 
            producer.producer_name AS producer_name, 
            region_name, 
            sponsored, 
            recipe_name, 
            recipe.id as recipeid, 
            round(avg(grade_id), 0) as nota
            from 
            wine, 
            inner join producer on wine.producer_id = producer.id  
            inner join winetype on wine.winetype_id = winetype.id
            inner join region on wine.region_id = region.id 
            left join pairing on wine.id = pairing.wine_id 
            left join recipe on recipe.id = pairing.recipe_id 
            left join rating rating.pairing_id = pairing.id 
        group by 
            wine.id,
            wine_name, 
            winetype_name, 
            wine_img, 
            producer.producer_name, 
            region_name, 
            sponsored, 
            recipe_name, 
            recipe.id
    ) as temp 
    left join grade on grade.id = temp.nota 
order by 
    sponsored desc, 
    nota desc;

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

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