sql – Postgres中的左外连接不返回Null值

下载包罗下载时刻,下载时刻ID和buno ID.
妨碍由妨碍代码,下载时刻ID,状态和范例构成.下载也许有很多错误,可以加载下载时ID.

给定一组妨碍代码,功效必需包括每个具有响应妨碍计数的妨碍代码.假如在下载中未找到妨碍代码,则必需返回妨碍代码,妨碍计数为零.

题目好像必要一个OUTER JOIN,可是没有看到它在Postgres上按预期事变,由于它好像没有从LEFT表返回带有空值的荟萃.

查询如下,为简捷起见省略了一些细节：

SELECT  f.faultcode,f.downloadtimeid,d.downloadtime,count(*) as faultcount 
FROM    download_time d 
LEFT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
    AND f.faultcode IN (1000,1100)
    AND f.statusid IN(2,4)
WHERE (d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid,f.faultcode

第二天,我编辑了以表现谜底.全部谜底都很靠近,并有各类百般的辅佐.然而,JayC的谜底是最靠近的.这是最终的SQL,只有作为WHERE子句回收妨碍代码IN语句的变动：

SELECT  f.faultcode,count(*) as faultcount
FROM    download_time d  
RIGHT OUTER JOIN fs_fault f ON f.downloadtimeid = d.id
        AND f.statusid IN(2,4)
        AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012'
        AND d.bunoid = 166501
WHERE f.faultcode IN (1000,1100)
GROUP BY d.bunoid,f.faultcode

感谢,统统都是为了你的辅佐！喜好这个网站！

办理要领

SELECT  f.faultcode,count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND f.faultcode IN (1000,4)
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
GROUP BY d.bunoid,f.faultcode

我以为应该沟通的另一种方法是

SELECT  f.faultcode,count(*) as faultcount 
FROM    download_time d 
RIGHT OUTER JOIN fs_fault f ON 
    f.downloadtimeid = d.id
    AND d.downloadtime BETWEEN '04/11/2011' AND '05/01/2012')
    AND d.bunoid = 166501
WHERE
    f.faultcode IN (1000,4)
GROUP BY d.bunoid,f.faultcode

由于fs_fault的过滤器要求不严酷. (而你的SQL引擎无论怎样城市改变这一点).

编辑：这是一个SQLFiddle演示过滤子句与where子句的过滤.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!