mysql语句如何在所有行都等于null的查询中跳过行

zqry0prt  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(351)

我创建了一个查询,用以下代码构造一个表:

SELECT Date, 
    SUM(CASE WHEN UniqueId = 'NonComLong' THEN Value ELSE NULL END) AS 'NonComLong',
    SUM(CASE WHEN UniqueId = 'NonComShort' THEN Value ELSE NULL END) AS 'NonComShort',
        SUM(CASE WHEN UniqueId = 'NonComNetLong' THEN Value ELSE NULL END) AS 'NonComNetLong',
        SUM(CASE WHEN UniqueId = 'NonComPos' THEN Value ELSE NULL END) AS 'NonComPos',
        SUM(CASE WHEN UniqueId = 'TotalPositions' THEN Value ELSE NULL END) AS 'TotalPositions',
        SUM(CASE WHEN UniqueId = 'NonComPosShare' THEN Value ELSE NULL END) AS 'NonComPosShare'
FROM fundamental_data WHERE AttributeId = 160 AND Date >= '2011-06-14'
GROUP BY Date
ORDER BY Date;

上面的代码返回下表:
表的图像
我想写一篇文章,如果除date列以外的所有列都返回null,我可以跳过一行。有可能吗?
提前谢谢!

cig3rfwq

cig3rfwq1#

假设那一列 value 不是 null 如果可以,您可以将另一个条件添加到 WHERE 确保聚合函数中至少满足一个条件的子句:

SELECT ...
FROM fundamental_data 
WHERE 
    AttributeId = 160 
    AND Date >= '2011-06-14'
    AND UniqueId IN (
        'NonComLong',
        'NonComShort',
        'NonComNetLong',
        'NonComPos',
        'TotalPositions',
        'NonComPosShare'
    )

如果 value 可能是 null ,您只需添加另一个条件:

WHERE
    ...
    AND value IS NOT NULL

相关问题