SQL运算中的空值处理技巧

我们在SQL中计算N列数值的求和，有时遇到某列中存在空值，空值null非数值，不参与数值计算，由此导致计算结果出错，下面我们看一个例子。

原数据表如下：

ID	姓名	身份证号码	联系电话	分值1	分值2	分值3	分值4
F001A006	李一	ABCDEF198702010031	15609331207	0.3	0.1	0.2	0.4
F001A007	李二	ABCDEF198802050036	15609331208	0.3	0.1	null	0.4
F001A008	李三	ABCDEF198903010025	15609331209	null	0.1	null	0.4
I001A001	张一	ABCDEF197202150014	13909331111	null	0.1	0.2	0.4
I001A002	张二	ABCDEF197311060054	13909331112	null	null	0.2	0.4
I001A003	张三	ABCDEF197410030011	13909331113	0.3	0.1	0.2	null
I001A004	张四	ABCDEF197508060035	13909331114	null	null	0.2	null
I001A005	张五	ABCDEF197609100018	13909331115	0.4	null	0.2	null

SQL查询代码如下：

SELECT
	ID,
	姓名,
	身份证号码,
	联系电话,
	户籍地,
	住址,
	(分值1 + 分值2 + 分值3 + 分值4 ) AS 总分 
FROM
	模拟表;

查询结果如下：

ID	姓名	身份证号码	联系电话	总分
F001A006	李一	ABCDEF198702010031	15609331207	1
F001A007	李二	ABCDEF198802050036	15609331208	null
F001A008	李三	ABCDEF198903010025	15609331209	null
I001A001	张一	ABCDEF197202150014	13909331111	null
I001A002	张二	ABCDEF197311060054	13909331112	null
I001A003	张三	ABCDEF197410030011	13909331113	null
I001A004	张四	ABCDEF197508060035	13909331114	null
I001A005	张五	ABCDEF197609100018	13909331115	null

我们可以看到分值1，分值2，分值3，三个列中存在空值，导致运算结果为NULL。

解决以上问题可以使用COALESCE()和ROUND()这两个SQL函数。

COALESCE()函数会检查第一个参数是否为空，如果为空，则返回第二个参数的值，否则返回第一个参数的值。同时它还允许你指定多个可能的替代值。

ROUND()函数是SQL中的一个内置函数，用于四舍五入数字到指定的小数位数。

以上SQL查询语句可以写成如下：

SELECT
	ID,
	姓名,
	身份证号码,
	联系电话,
	户籍地,
	住址,
	ROUND((
			COALESCE (分值1, 0 ) + COALESCE (分值2, 0 ) + COALESCE (分值3, 0 ) + COALESCE (分值4, 0 )),
		2 
	) AS 总分 
FROM
	模拟表;

以上SQL查询语句外层嵌套使用了ROUND()函数，计算结果保留小数点后2位，内层的COALESCE()函数，首先检测分值1、分值2、分值3、分值4列是否存在空值，如果 存在空值则将其替换为数值0，最后计算结果。

新的查询结果如下：

ID	姓名	身份证号码	联系电话	总分
F001A006	李一	ABCDEF198702010031	15609331207	1
F001A007	李二	ABCDEF198802050036	15609331208	0.8
F001A008	李三	ABCDEF198903010025	15609331209	0.5
I001A001	张一	ABCDEF197202150014	13909331111	0.7
I001A002	张二	ABCDEF197311060054	13909331112	0.6
I001A003	张三	ABCDEF197410030011	13909331113	0.6
I001A004	张四	ABCDEF197508060035	13909331114	0.2
I001A005	张五	ABCDEF197609100018	13909331115	0.6