如何忽略子查询中的(null或0)值

Question

我将这个表命名为(sale):

金额货币tbody> <<tr>

类别
cat1	10000	美元
cat2	5000	ERU
cat3	15000	SAR
cat2	30000	美元
cat1	45000	SAR
cat3	7000	ERU
cat2	3000	ERU
cat4	0	美元

如果您切换到条件聚合，这将是更高效的方式，那么您可以使用HAVING。当您想要排除'EUR'的行时，您需要在其中使用CASE表达式:

SELECT S.Category,
SUM(CASE currency WHEN 'USD' THEN amount ELSE 0 END) AS USD,
--SUM(CASE currency WHEN 'EUR' THEN amount ELSE 0 END) AS EUR, --Seems you don't want EUR rows
SUM(CASE currency WHEN 'SAR' THEN amount ELSE 0 END) AS SAR,
SUM(CASE currency WHEN 'ERU' THEN amount ELSE 0 END) AS ERU
FROM dbo.Sale S
GROUP BY S.Category
HAVING SUM(CASE WHEN currency IN ('USD','SAR','ERU') THEN amount END) != 0;

由于货币'EUR'似乎是一个印刷错误，您可能只需要:

HAVING SUM(amount) != 0;