SQL：当一列具有空值时对 3 列求和？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1088648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: sum 3 columns when one column has a null value?
提问by Yves
SELECT
sum(TotalHoursM)
+ (TotalHoursT)
+ (TotalHoursW)
+ (TotalHoursTH)
+ (TotalHoursF)
AS TOTAL
FROM LeaveRequest
回答by Mitchel Sellers
If the column has a 0 value, you are fine, my guess is that you have a problem with a Null value, in that case you would need to use IsNull(Column, 0)to ensure it is always 0 at minimum.
如果该列的值是 0，则没问题，我的猜测是 Null 值有问题，在这种情况下，您需要使用IsNull(Column, 0)它来确保它至少始终为 0。
回答by Jim Reineri
The previous answers using the ISNULLfunction are correct only for MS Sql Server. The COALESCEfunction will also work in SQL Server. But will also work in standard SQL database systems. In the given example:
使用该ISNULL函数的先前答案仅适用于 MS Sql Server。该COALESCE函数也适用于 SQL Server。但也适用于标准 SQL 数据库系统。在给定的例子中：
SELECT sum(COALESCE(TotalHoursM,0))
+ COALESCE(TotalHoursT,0)
+ COALESCE(TotalHoursW,0)
+ COALESCE(TotalHoursTH,0)
+ COALESCE(TotalHoursF,0) AS TOTAL FROM LeaveRequest
This is identical to the ISNULLsolution with the only difference being the name of the function. Both work in SQL Server but, COALESCEis ANSI standard and ISNULLis not. Also, COALESCEis more flexible.
ISNULLwill only work with two parameters. If the first parameter is NULL then the value of the second parameter is returned, else the value of the first is returned.
COALESCE will take 2 to 'n' (I don't know the limit of 'n') parameters and return the value of the first parameter that is not NULL. When there are only two parameters the effect is the same as ISNULL.
这与ISNULL解决方案相同，唯一的区别是函数的名称。两者都在 SQL Server 中工作，但是COALESCE是 ANSI 标准，ISNULL不是。此外，COALESCE更灵活。
ISNULL仅适用于两个参数。如果第一个参数为 NULL，则返回第二个参数的值，否则返回第一个参数的值。COALESCE 将采用 2 到 'n'（我不知道 'n' 的限制）参数并返回第一个不是 的参数的值NULL。当只有两个参数时，效果与 相同ISNULL。
回答by gjutras
SELECT sum(isnull(TotalHoursM,0))
+ isnull(TotalHoursT,0)
+ isnull(TotalHoursW,0)
+ isnull(TotalHoursTH,0)
+ isnull(TotalHoursF,0)
AS TOTAL FROM LeaveRequest
回答by omkarv
Just for reference, the equivalent statement for MySQL is: IFNull(Column,0).
仅供参考，MySQL 的等效语句是： IFNull(Column,0)。
This statement evaluates as the column value if not null, otherwise it is evaluated as 0.
如果不为空，则此语句计算为列值，否则计算为 0。
回答by Colin
You can use ISNULL:
您可以使用ISNULL：
ISNULL(field, VALUEINCASEOFNULL)
回答by KM.
looks like you want to SUM all the columns (I'm not sure where "sum 3 columns" comes from), not just TotalHoursM, so try this:
看起来您想对所有列求和（我不确定“求和 3 列”从何而来），而不仅仅是 TotalHoursM，所以试试这个：
SELECT
SUM( ISNULL(TotalHoursM ,0)
+ ISNULL(TotalHoursT ,0)
+ ISNULL(TotalHoursW ,0)
+ ISNULL(TotalHoursTH ,0)
+ ISNULL(TotalHoursF ,0)
) AS TOTAL
FROM LeaveRequest
回答by David
You can also use nvl(Column,0)
你也可以使用 nvl(Column,0)
回答by Mohamed Omer
If you want to avoid the null value use IsNull(Column, 1)
如果要避免空值，请使用 IsNull(Column, 1)
回答by Jerome
I would try this:
我会试试这个：
select sum (case when TotalHousM is null then 0 else TotalHousM end)
+ (case when TotalHousT is null then 0 else TotalHousT end)
+ (case when TotalHousW is null then 0 else TotalHousW end)
+ (case when TotalHousTH is null then 0 else TotalHousTH end)
+ (case when TotalHousF is null then 0 else TotalHousF end)
as Total
From LeaveRequest
