锋利的SQL: 取出多列中的非空值



京东：http://item.jd.com/10380652.html

当列中包含空值（NULL）时，由于空值的特殊性，在进行列计算时会带来一些问题。

19.6.1 姓名问题处理

这是在做一个客户管理系统时遇到的问题。为照顾到一些国外客户，在设计客户名称列时使用了3列，分别用来存储名字（FirstName）、中间名（MiddleName）和姓氏（Last Name）。将姓氏和名字分别存储在业务处理上能够更加灵活，例如，在给客户发送电子邮件时可以只获取名字，像Dear John这样称呼会使客户感觉更加亲切，而在给客户邮寄东西时，则使用姓名全称能够更加准确地定位客户，这时候则需要将三列数据加在一起。现在我们需要讨论的就是如何正确的返回姓名全称。

首先来创建示例表：

CREATE TABLE Customers

(

 first_name  char(10) NOT NULL,

 middle_name char(10),

 last_name   char(10)

);

INSERT INTO Customers

VALUES

   ('Jack',   'A.', 'Tuszynski'),

   ('Sisley', NULL, 'Weilage'),

   ('Eddie',  NULL, NULL);

如果直接进行简单的相加，你会发现在表19-19所示的查询结果中，只有第1行是正确的，第2行和第3行由于包含了NULL值，返回了NULL。

SELECT first_name + ' ' + middle_name + ' ' +last_name AS full_name

FROM Customers;

表19-19                                                                       查询结果

full_name

Jack       A.         Tuszynski

NULL

NULL

下面的语句使用CASE表达式进行NULL值判断，得到了正确的查询结果，如表19-20所示。

SELECT first_name +

       CASEWHEN (middle_name IS NOT NULL) THEN middle_name

           ELSE ''

       END +

       CASEWHEN (last_name IS NOT NULL) THEN last_name

           ELSE ''

       ENDAS full_name

FROM Customers; 

表19-20                                                                       查询结果

full_name

Jack       A.         Tuszynski

Sisley    Weilage

Eddie

还有一种方法就是使用COALESCE(expression[ ,...n ] )函数，它可以返回表达式列表中不为空的第一个表达式。下面语句返回与上面相同的结果，语句中的COALESCE(middle_name, '')包含有middle_name和空字符串两个表达式，如果middle_name为空，则返回后面的空字符串，否则直接返回middle_name。

SELECT first_name +

      COALESCE(middle_name, '') +

      COALESCE(last_name, '') AS full_name

FROM Customers;

下面的语句则是使用RTRIM()函数去除了每列后面的多余空格。

SELECT RTRIM(first_name) + ' ' +

      RTRIM(COALESCE(middle_name, '')) + ' ' +

      RTRIM(COALESCE(last_name, '')) AS full_name

FROM Customers;

19.6.2 工资问题处理

这是在做工资计算时遇到的一个问题。在如表19-21所示的工资表中，Jack和Mary是按小时计算工资报酬，hour_sum中是累计工作小时，pay_per_h中是每小时的报酬额。而Joy和Nancy是按天计算工资报酬，day_sum中是累计工作天数，pay_per_day中是每天的报酬额。

表19-21                                                                工资表（Salary）

emp_name	hour_sum	pay_per_h	day_sum	pay_per_day
Jack	200	20.00	NULL	NULL
Mary	300	30.00	NULL	NULL
Joy	NULL	NULL	20	120.00
Nancy	NULL	NULL	22	100.00

现在要计算应付给每个人的工资总额，为了能够正确处理NULL值，这里仍旧使用COALESCE()函数，语句如下：

SELECT emp_name, COALESCE(hour_sum * pay_per_h,day_sum * pay_per_day) AS pay_all

FROM Salary;

返回结果如表19-22所示。

表19-22                                                                       工资总额

emp_name	pay_all
Jack	4000.00
Mary	9000.00
Joy	2400.00
Nancy	2200.00