SQL 在 oracle 中对空值的列求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/590551/
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
Sum columns with null values in oracle
提问by Berek Bryan
I want to add two numbers together but when one of those numbers is null then the result is null. Is there a way around this. I could simply do it in the code but I would rather have it done in the query. This is a oracle database.
我想将两个数字相加，但是当这些数字之一为空时，结果为空。有没有解决的办法。我可以简单地在代码中完成，但我宁愿在查询中完成。这是一个oracle数据库。
The table structure
表结构
hours_t
type craft regular overtime
A 1 5 0
A 1 3 1
B 2 9 <null>
B 1 4 4
The query
查询
select type, craft, sum(regular + overtime) as total_hours
from hours_t
group by type, craft
order by type, craft
The unwanted results
不想要的结果
type craft total_hours
A 1 9
B 1 8
B 2 <null>
The wanted results
想要的结果
type craft total_hours
A 1 9
B 1 8
B 2 9
回答by zendar
NVL(value, default) is the function you are looking for.
NVL(value, default) 是您正在寻找的功能。
select type, craft, sum(NVL(regular, 0) + NVL(overtime, 0) ) as total_hours
from hours_t
group by type, craft
order by type, craft
Oracle have 5 NULL-related functions:
Oracle 有 5 个与 NULL 相关的函数：
- NVL
- NVL2
- COALESCE
- NULLIF
- LNNVL
- NVL
- NVL2
- 合并
- NULLIF
- 神经网络
NVL:
NVL:
NVL(expr1, expr2)
NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
NVL 允许您用查询结果中的字符串替换 null（以空白形式返回）。如果 expr1 为空，则 NVL 返回 expr2。如果 expr1 不为空，则 NVL 返回 expr1。
NVL2:
NVL2:
NVL2(expr1, expr2, expr3)
NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.
NVL2 允许您根据指定的表达式是否为空来确定查询返回的值。如果 expr1 不为空，则 NVL2 返回 expr2。如果 expr1 为空，则 NVL2 返回 expr3。
COALESCE(expr1, expr2, ...)
COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.
COALESCE 返回表达式列表中的第一个非空 expr。至少一个 expr 不能是文字 NULL。如果所有出现的 expr 计算结果为 null，则该函数返回 null。
NULLIF(expr1, expr2)
NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.
NULLIF 比较 expr1 和 expr2。如果它们相等，则函数返回 null。如果它们不相等，则函数返回 expr1。您不能为 expr1 指定文字 NULL。
LNNVL(condition)
LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null.
当条件的一个或两个操作数可能为空时，LNNVL 提供了一种评估条件的简洁方法。
More info on Oracle SQL Functions
有关Oracle SQL 函数的更多信息
回答by Tony Andrews
select type, craft, sum(nvl(regular,0) + nvl(overtime,0)) as total_hours
from hours_t
group by type, craft
order by type, craft
回答by cletus
The other answers regarding the use of nvl() are correct however none seem to address a more salient point:
关于使用 nvl() 的其他答案是正确的，但似乎没有一个更突出的问题：
Should you even have NULLs in this column?
您甚至应该在此列中包含 NULL 吗？
Do they have a meaning other than 0?
它们是否具有除 0 以外的含义？
This seems like a case where you should have a NOT NULL DEFAULT 0 on th ecolumn
这似乎是您应该在 ecolumn 上设置 NOT NULL DEFAULT 0 的情况
回答by JosephStyons
The top-rated answer with NVL is totally valid. If you have any interest in making your SQL code more portable, you might want to use CASE, which is supported with the same syntax in both Oracle and SQL Server:
NVL 评分最高的答案是完全有效的。如果您有兴趣使您的 SQL 代码更具可移植性，您可能需要使用 CASE，它在 Oracle 和 SQL Server 中都支持相同的语法：
select
type,craft,
SUM(
case when regular is null
then 0
else regular
end
+
case when overtime is null
then 0
else overtime
end
) as total_hours
from
hours_t
group by
type
,craft
order by
type
,craft
回答by Nikhil
In some cases, nvl(sum(column_name),0) is also required. You may want to consider your scenarios.
在某些情况下，还需要 nvl(sum(column_name),0)。您可能需要考虑您的场景。
For example, I am trying to fetch the sum of a particular column, from a particular table based on certain conditions. Based on the conditions,
例如，我试图根据特定条件从特定表中获取特定列的总和。根据条件，
- one or more rows exist in the table. In this case I want the sum.
- rows do not exist. In this case I want 0.
- 表中存在一或多行。在这种情况下，我想要总和。
- 行不存在。在这种情况下，我想要 0。
If you use sum(nvl(column_name,0)) here, it would give you null. What you might want is nvl(sum(column_name),0).
如果你在这里使用 sum(nvl(column_name,0)) ，它会给你空值。您可能想要的是 nvl(sum(column_name),0)。
This may be required especially when you are passing this result to, say, java, have the datatype as number there because then this will not require special null handling.
这可能是必需的，尤其是当您将此结果传递给 java 时，将数据类型作为数字，因为这样就不需要特殊的 null 处理。
回答by Turnkey
You need to use the NVL function, e.g.
您需要使用 NVL 功能，例如
SUM(NVL(regular,0) + NVL(overtime,0))
SUM(NVL(常规，0) + NVL(加班，0))
回答by Quassnoi
select type, craft, sum(NVL(regular, 0) + NVL(overtime, 0)) as total_hours
from hours_t
group by type, craft
order by type, craft
回答by Kumar SR
Code:
代码：
select type, craft, sum(coalesce( regular + overtime, regular, overtime)) as total_hours
from hours_t
group by type, craft
order by type, craft
