COALESCE 函数
功能
返回列表中的第一个非空表达式。
语法
COALESCE ( expression, expression [ , ...] )
参数
expression 任意表达式。
标准和兼容性
SQL/92 SQL/92。
SQL/99 核心特性。
示例
下面的语句返回值 34。
SELECT COALESCE( NULL, 34, 13, 0 )
备注
如果所有参数均为 NULL,则 COALESCE 返回 NULL。
COALESCE(expression1,...n) 与此 CASE 函数等效:
CASE WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN ELSE NULLEND
尽管 ISNULL 等效于 COALESCE,但它们的行为是不同的。包含具有非空参数的 ISNULL
的表达式将视为 NOT NULL,而包含具有非空参数的 COALESCE 的表达式将视为 NULL。
使用COALESCE的秘密在于大部分包含空值的表达式最终将返回空值(连接操作符“||”
是一个值得注意的例外)。例如,空值加任何值都是空值,空值乘任何值也都是空值,
依此类推。
这样您就可以构建一系列表达式,每个表达式的结果是空值或非空,这就像一个CASE语
句,表达式按照顺序进行测试,第一个满足条件的值就确定了结果。
功能
返回列表中的第一个非空表达式。
语法
COALESCE ( expression, expression [ , ...] )
参数
expression 任意表达式。
标准和兼容性
SQL/92 SQL/92。
SQL/99 核心特性。
示例
下面的语句返回值 34。
SELECT COALESCE( NULL, 34, 13, 0 )
备注
如果所有参数均为 NULL,则 COALESCE 返回 NULL。
COALESCE(expression1,...n) 与此 CASE 函数等效:
CASE WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN ELSE NULLEND
尽管 ISNULL 等效于 COALESCE,但它们的行为是不同的。包含具有非空参数的 ISNULL
的表达式将视为 NOT NULL,而包含具有非空参数的 COALESCE 的表达式将视为 NULL。
使用COALESCE的秘密在于大部分包含空值的表达式最终将返回空值(连接操作符“||”
是一个值得注意的例外)。例如,空值加任何值都是空值,空值乘任何值也都是空值,
依此类推。
这样您就可以构建一系列表达式,每个表达式的结果是空值或非空,这就像一个CASE语
句,表达式按照顺序进行测试,第一个满足条件的值就确定了结果。
EG:
create
table
表1(id
int
,no
int
,数学1
int
,语文
int
)
create table 表2(id int ,no int ,数学2 int ,语文 int )
create table 表3(id int ,no int ,数学3 int ,语文3 int )
insert 表1 select 1 , 1 , 100 , 90
insert 表2 select 1 , 2 , 85 , 70
insert 表3 select 1 , 3 , 92 , 60
select id = coalesce (a.id,b.id,c.id),no = coalesce (a.no,b.no,c.no),a.数学1,b.数学2,c.数学3, coalesce (a.语文,b.语文),c.语文3
from 表1 a full join 表2 b on a.id = b.id and a.no = b.no
full join 表3 c on c.id = b.id and c.no = b.no
/*
id no 数学1 数学2 数学3 语文3
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 100 NULL NULL 90 NULL
1 2 NULL 85 NULL 70 NULL
1 3 NULL NULL 92 NULL 60
(3 row(s) affected)
*/
create table 表2(id int ,no int ,数学2 int ,语文 int )
create table 表3(id int ,no int ,数学3 int ,语文3 int )
insert 表1 select 1 , 1 , 100 , 90
insert 表2 select 1 , 2 , 85 , 70
insert 表3 select 1 , 3 , 92 , 60
select id = coalesce (a.id,b.id,c.id),no = coalesce (a.no,b.no,c.no),a.数学1,b.数学2,c.数学3, coalesce (a.语文,b.语文),c.语文3
from 表1 a full join 表2 b on a.id = b.id and a.no = b.no
full join 表3 c on c.id = b.id and c.no = b.no
/*
id no 数学1 数学2 数学3 语文3
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 100 NULL NULL 90 NULL
1 2 NULL 85 NULL 70 NULL
1 3 NULL NULL 92 NULL 60
(3 row(s) affected)
*/
1、删除表、创建表、注释、插入数据操作。
DROP TABLE AA_WEEK;
CREATE TABLE AA_WEEK(
CODE SMALLINT NOT NULL,
EN_NAME VARCHAR(20) NOT NULL,
ABB VARCHAR(20),
CONSTRAINT P_AA_WEEK_CODE PRIMARY KEY (CODE)
);
COMMENT ON TABLE AA_WEEK IS
'星期';
COMMENT ON AA_WEEK
(CODE IS '星期代码',
EN_NAME IS '英文星期名',
ABB IS '英文缩写'
);
INSERT INTO AA_WEEK VALUES
(1,'Monday','Mon'),
(2,'Tuesday','Tues'),
(3,'Wednesday','Wed'),
(4,'Thursday','Thurs'),
(5,'Friday','Friday'),
(6,'Saturday','Sat'),
(7,'Sunday','Sun');
COMMIT;
2、CASE查询
两种语法模式:
(1):CASE
WHEN 条件 THEN 结果1
ELSE 结果2
END
(2):CASE 表达式1
WHEN 表达式2 THEN 结果1
ELSE 结果2
END
-- 单值表达式作为条件
SELECT
CASE CODE
WHEN 1 THEN '一'
WHEN 2 THEN '二'
WHEN 3 THEN '三'
WHEN 4 THEN '四'
WHEN 5 THEN '五'
WHEN 6 THEN '六'
WHEN 7 THEN '七'
ELSE 'CODE错误!'
END AS CN_CODE,
EN_NAME
FROM AA_WEEK;
CN_CODE EN_NAME
--------------------
'一' 'Monday'
'二' 'Tuesday'
'三' 'Wednesday'
'四' 'Thursday'
'五' 'Friday'
'六' 'Saturday'
'七' 'Sunday'
'CODE错误!' 'HAHA'
-- 条件直接写到when后面
SELECT
CASE
WHEN CODE = 1 THEN '一'
WHEN CODE = 2 THEN '二'
WHEN CODE = 3 THEN '三'
WHEN CODE = 4 THEN '四'
WHEN CODE = 5 THEN '五'
WHEN CODE = 6 THEN '六'
WHEN CODE = 7 THEN '七'
ELSE 'CODE错误!'
END AS CN_CODE,
EN_NAME
FROM AA_WEEK;
CN_CODE EN_NAME
--------------------
'一' 'Monday'
'二' 'Tuesday'
'三' 'Wednesday'
'四' 'Thursday'
'五' 'Friday'
'六' 'Saturday'
'七' 'Sunday'
'CODE错误!' 'HAHA'
二、IF语句格式(仅仅限于DB2过程、触发器、函数中使用)
IF (条件)
THEN 语句;
ELSEIF (条件)
THEN 语句;
ELSEIF (条件)
THEN 语句;
... ...
ELSE
语句;
END IF;