如何用sql实现两个字段相加？

Question

有两个float类型的字段A,B
但是值可以为空
如果用select A+B
则当一个字段的值为null 另一个有值的时候
查询出来的结果还是null
如何实现以下结果
A B
null null
10 null
null 20
10 20

如何能够查出结果为
sum
null
10
20
30

Answer 1

MS_SQL
select nullif(isnull(A,0)+isnull(B,0),0) as [Sum] from table1

Oracle
用 select case when nvl(A,0)+nvl(B,0)=0 then null else nvl(A,0)+nvl(B,0)end as [Sum] from table1

Answer 2

mssql

--> 生成测试数据表: [t2]
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
CREATE TABLE T1
( A FLOAT,
B FLOAT)
INSERT INTO T1
SELECT NULL,NULL UNION ALL
SELECT 10,NULL UNION ALL
SELECT NULL,20 UNION ALL
SELECT 20,NULL
-->SQL查询如下:
SELECT COALESCE(A,B) FROM T1
--

Answer 3

select isnull(A,0)+isnull(B,0)

Answer 4

select sum(case when a is null and b is null then null else isnull(a,0) + isnull(b,0)) as [sum]
from table