SQL 3列的SQL总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6559472/
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 of 3 columns
提问by Adam Strudwick
I know this question might seem a little basic stuff but I want to make sure I get the right syntax because this line of code will run often:
我知道这个问题可能看起来有点基本，但我想确保我得到正确的语法，因为这行代码会经常运行：
I want to sum columns A, B and C of table "alphabet" for all rows which have an id included in my IN clause.
我想对包含在我的 IN 子句中的 id 的所有行的表“字母表”的 A、B 和 C 列求和。
This is how I would do it but I'd like a confirmation if possible:
这就是我的做法，但如果可能的话，我想确认一下：
SELECT SUM(A + B + C) as "subtotal" FROM alphabet WHERE id IN ('1','5','378');
回答by btilly
If idis not a string, you shouldn't quote those values.
如果id不是字符串，则不应引用这些值。
SELECT SUM(A + B + C) as "subtotal" FROM alphabet WHERE id IN (1,5,378);
This should work, but may may have one non-obvious consequence. If any row has Anot null and one of the others null, that row will drop out of the summation because a null plus anything else is null, and nulls are ignored by the SUMoperator. Thus it may be safer to write:
这应该有效，但可能会产生一个不明显的后果。如果任何行A不为 null 而其他行之一为 null，则该行将退出求和，因为 null 加上其他任何内容都是 null，并且SUM运算符会忽略 null 。因此，编写以下代码可能更安全：
SELECT SUM(A) + SUM(B) + SUM(C) as "subtotal" FROM alphabet WHERE id IN (1,5,378);
This suffers from the same potential problem, but it is less likely to happen because an entire column would have to be null. There are various dialect specific ways to defend against that problem if you are still concerned. The most portable is the painfully verbose:
这会遇到相同的潜在问题，但不太可能发生，因为整个列必须为空。如果您仍然担心，有多种特定于方言的方法可以解决该问题。最便携的是痛苦的冗长：
SELECT SUM(
CASE
WHEN A IS NULL
THEN 0
ELSE A
END
+
CASE
WHEN B IS NULL
THEN 0
ELSE B
END
+
CASE
WHEN c IS NULL
THEN 0
ELSE C
END
) as "subtotal"
FROM alphabet
WHERE id IN (1,5,378);
回答by duffymo
"Run often" suggests that performance will be a concern. If that's true, make sure the the id has an index on it. If it's a primary key, it'll have an index by default.
“经常运行”表明性能将是一个问题。如果这是真的，请确保该 id 上有一个索引。如果它是主键，则默认情况下它会有一个索引。
If you don't have an index, you'll end up doing a table scan - examining each and every row. Performance will get worse as the size of the table grows.
如果您没有索引，您将最终进行表扫描 - 检查每一行。随着表大小的增加，性能会变得更糟。
Do an EXPLAIN PLAN on your query and make sure you don't see a TABLE SCAN anywhere.
对您的查询执行 EXPLAIN PLAN 并确保您没有在任何地方看到 TABLE SCAN。
回答by Sikor
declare
@a int = 0,
@b int = 5,
@c int = NULL
select
ISNULL(@a, 0) + ISNULL(@b, 0) + ISNULL(@c, 0)
