SQL DB2联合跳过NULL/0行

Question

有没有简单的方法来跳过联合空行？ 我在DB2上。

实施例：

SELECT 'FIRST', sum(value) FROM SYSIBM.SYSDUMMY1 
UNION 
SELECT 'SECOND', sum(value) FROM SYSIBM.SYSDUMMY1 
UNION 
SELECT 'THIRD', sum(value) FROM SYSIBM.SYSDUMMY1 

结果：

1---------|2----- 
    FIRST 1 
    SECOND NULL/0 
    THIRD 3 

所以，如果工会中的一个具有0或NULL它应跳过：

估计示例的结果：

1---------|2----- 
    FIRST 1 
    THIRD 3 

Answer 1

是的，你c通过使用HAVING过滤器检查IS NOT NULL来做到这一点。

SELECT 'FIRST', sum(value) 
    FROM SYSIBM.SYSDUMMY1 GROUP BY 'FIRST' HAVING sum(value) IS NOT NULL 

UNION 

SELECT 'SECOND', sum(value) 
    FROM SYSIBM.SYSDUMMY1 GROUP BY 'SECOND' HAVING sum(value) IS NOT NULL 

UNION 

SELECT 'THIRD', sum(value) 
    FROM SYSIBM.SYSDUMMY1 GROUP BY 'THIRD' HAVING sum(value) IS NOT NULL 

如果任何sum(value)回归NULL，的相关SELECT语句将返回零行由于GROUP BY条款，导致相应的行遗漏。

如果你想省略基于要么一排NULL或一个零和，添加一个AND一起像这样：

SELECT 'FIRST', sum(value) FROM SYSIBM.SYSDUMMY1 GROUP BY 'FIRST' HAVING sum(value) IS NOT NULL AND NOT sum(value) = 0 
UNION 
SELECT 'SECOND', sum(value) FROM SYSIBM.SYSDUMMY1 GROUP BY 'SECOND' HAVING sum(value) IS NOT NULL AND NOT sum(value) = 0 
UNION 
SELECT 'THIRD', sum(value) FROM SYSIBM.SYSDUMMY1 GROUP BY 'THIRD' HAVING sum(value) IS NOT NULL AND NOT sum(value) = 0 

此外，还可以通过增加查询的可能效率将UNION更改为UNION ALL，因为您在最终结果中不会有重复的行。 UNION做了重复的检查，在这种情况下永远不会触发。

Answer 2

使用子查询。我也建议union all：

SELECT col, val 
FROM (SELECT 'FIRST' as col, sum(value) as val FROM SYSIBM.SYSDUMMY1 
     UNION ALL 
     SELECT 'SECOND' as col, sum(value) as val FROM SYSIBM.SYSDUMMY1 
     UNION ALL 
     SELECT 'THIRD' as col, sum(value) as val FROM SYSIBM.SYSDUMMY1 
    ) t 
WHERE val IS NOT NULL AND val > 0; 

注：

• UNION招致开销删除重复。使用UNION ALL可以避免这种开销。
• 比较val IS NOT NULL是多余的。但它明确表达你想要的条件。