mysql - SQL 除以 SELECT 语句中的零错误

Question

我是 SQL 的新手，需要帮助。我的 SELECT 语句中出现以下除零错误:

SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'),  ')','')) as money)) - SUM(Cast((replace(replace(replace (p.[Total Cost], '$', ''), '(','-'),  ')','')) as money)) / SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'),  ')','')) as money)) as new_bal

我知道我需要使用 CASE，但我不确定应用程序。

Answer 1

你可以使用 NULLIF 在以下内容中:

NULLIF(expression1, 0)

在你的情况下它将是:

SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'),  ')','')) as money)) - SUM(Cast((replace(replace(replace (p.[Total Cost], '$', ''), '(','-'),  ')','')) as money)) / NULLIF(SUM(Cast((replace(replace(replace (p.[Total Sales], '$', ''), '(','-'),  ')','')) as money)),0) as new_bal