跨多列添加NULL值

Question

Col1    Col2    Col3    SumCol
4       9     NULL      13
NULL      8       2       10
8       3     NULL      11
NULL      5       5       10

我有一个由Col1、Col2和Col3列填充的表，我正在尝试创建一个新列SumCol。我知道添加NULL值很烦人，所以我很感激的帮助

Answer 1

您可以在sql server 中使用以下查询

select id, col1, col2, col3, (coalesce(col1, 0) + coalesce(col2, 0) + coalesce(col3, 0)) total
from @tbl

或

select id, col1, col2, col3, (ISNULL(col1, 0) + ISNULL(col2, 0) + ISNULL(col3, 0)) total
from @tbl

Answer 2

使用XQuery或COALESCE()非常简单。

SQL#1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Col1 INT, Col2 INT, Col3 INT);
INSERT INTO @tbl (Col1, Col2, Col3) VALUES
(  4 , 9, NULL),
(NULL, 8,   2 ),
(  8 , 3, NULL),
(NULL, 5,   5 );
-- DDL and sample data population, end
SELECT ID, Col1, Col2, Col3
, x.value('sum(/root/*/text())', 'INT') AS Summary
FROM @tbl
CROSS APPLY (SELECT Col1, Col2, Col3 FOR XML PATH(''), TYPE, ROOT('root')) AS t(x);

SQL#2

根据@DaleK的建议，最常见的解决方案如下。

SELECT * 
, Summary = COALESCE(Col1,0) + COALESCE(Col2,0) + COALESCE(Col3,0) 
FROM @tbl;

SQL#3

针对Col1、Col2。。。，ColN场景。

SELECT ID, Col1, Col2, Col3
, x.value('sum(/root/*[not(local-name()="ID")]/text())', 'INT') AS Summary
FROM @tbl AS p
CROSS APPLY (SELECT * FROM @tbl AS c
WHERE c.ID = p.ID
FOR XML PATH(''), TYPE, ROOT('root')) AS t(x);

输出

+----+------+------+------+---------+
| ID | Col1 | Col2 | Col3 | Summary |
+----+------+------+------+---------+
|  1 | 4    |    9 | NULL |      13 |
|  2 | NULL |    8 | 2    |      10 |
|  3 | 8    |    3 | NULL |      11 |
|  4 | NULL |    5 | 5    |      10 |
+----+------+------+------+---------+

Answer 3

计算列通常更容易，如果您希望查询表的任何人都可以使用该计算：

ALTER TABLE YourTable
ADD COLUMN SumCol AS ISNULL(Col1, 0) + ISNULL(Col2, 0) + ISNULL(Col3, 0);