常用sql函数：COALESCE()进行空值处理、greatest函数和least函数

一、函数COALESCE()

1、函数介绍

　　主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下：

COALESCE ( expression,value1,value2……,valuen) 

　　第一个参数expression为待检测的表达式，而其后的参数个数不定。

　　COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。

　　如果expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返回value1；否则判断value2是否是空值，如果value2不为空值则返回value2；……以此类推，如果所有的表达式都为空值，则返回NULL。

2、具体应用

　　我们使用COALESCE()函数完成下面的功能，返回人员的“重要日期”： 如果出生日期不为空，则将出生日期做为“重要日期”，如果出生日期为空则判断注册日期是否为空，如果注册日期不为空则将注册日期做为“重要日期”，如果注册日期也为空则将“2008年8月8日”做为“重要日期”。实现此功能的SQL语句如下：

SELECT FName,FBirthDay,FRegDay, COALESCE(FBirthDay, FRegDay,'2008-08-08')  AS ImportDay  
FROM T_Person 

3、COALESCE()函数可以用来完成几乎所有的空值处理，不过在很多数据库系统中都提供了它的简化版，这些简化版中只接受两个变量，其参数格式如下：

　　MYSQL: IFNULL(expression,value)

　　MSSQLServer:  ISNULL(expression,value)

　　Oracle: NVL(expression,value) 

　　这几个函数的功能和COALESCE(expression,value)是等价的。

　　比如SQL语句用于返回人员的“重要日期”，如果出生日期不为空则将出生日期做为“重要日期”，如果出生日期为空则返回注册日期的值：

SELECT FBirthDay,FRegDay,  IFNULL(FBirthDay,FRegDay)  AS ImportDay  
FROM T_Person 

二、greatest函数和least函数

1、LEAST 返回值列表中最小值，  格式： LEAST(value1, value2, value3, …)

    含义： 返回value列表最小的值。

               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

               当value值列表中有一个为NULL，则返回NULL值。

2、GREATEST 返回值列表中最大值 ，  格式： GREATEST(value1, value2, value3, …)

    含义： 返回value列表最大的值。

               value列表必须是相同类型，也可以是一个表的同一行、不同列的值进行比较。

               当value值列表中有一个为NULL，则返回NULL值。

select greatest (1, 3, 2 ) from dual ;  -- 返回3
select greatest ( ‘A’, ‘B’, ‘C’ ) from dual ; -- 返回C
select greatest (null, ‘B’, ‘C’ ) from dual ; -- 返回null

三、SQL greatest() 函数实例详解实例

我在项目中有个实际用处：这里要返回最新的时间，时间有两个，一个是一级评论的时间、一个是二级评论的时间，要取一个最新的时间，就可以用这个 greatest 函数

GREATEST(ct.created_time, lmt.LastModifiedTime) LastModifiedTime

1、注意点：greatest(case_1, case_2, ...case_n)函数从表达式（列、常量、计算值）case_1, case_2, ... case_n等中找出最大的数返回，在比较时以case_1的数据类型为准。

2、数值 - case_1为数值型。按大小进行比较

　　部分为数值型，但是字符串可以根据case_1的数据类型通过隐式类型转换转成数值型：

SELECT greatest(2, '5', 12, 3, 16, 8, 9) A FROM DUAL; -- 16

　　部分为数值型，但是字符串不能通过隐式类型转换成数值型会报错，因为字符串A不能转换成数值型

SELECT greatest(2, 'A', 12, 3, 16, 8, 9) A FROM DUAL; -- 无效 SQL 语句

3、字符串 - case_1为字符型。按首字母进行比较（如果相等则向下比较）

-- 全部为字符型，首字母相等
SELECT greatest('A', 'B', 'C', 'D', 'E','GA', 'GAB') A FROM DUAL; -- GAB

-- 部分为字符型，会把非字符型转换成字符型
SELECT greatest('A', 6, 7, 5000, 'E', 'F','G') A FROM DUAL; -- G

4、时间 - case_1为时间类型

　　全部为时间类型 - 返回最大的时间

　　部分为时间类型，不能进行隐式类型转换

SELECT greatest(TO_DATE('2018-07-01','YYYY-MM-DD'),TO_DATE('2018-08-01','YYYY-MM-DD')) A FROM DUAL;
-- 2018/8/1
SELECT greatest(TO_DATE('2018-08-01','YYYY-MM-DD'),'2018-07-01') A FROM DUAL
-- 文字与格式字符串不匹配

5、空值 - 当case为函数的时候，不可避免的会产生空值

　　只要greatest的case有一个为NULL，都会返回NULL