SQL中的coalesce函数与case语句
来源:互联网 发布:软件是否属于无形资产 编辑:程序博客网 时间:2024/05/24 01:39
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)
*/
- SQL中的coalesce函数与case语句
- SQL中的coalesce函数与case语句
- COALESCE 函数 和CASE语句
- COALESCE 函数和case语句
- COALESCE 函数 和CASE语句
- SQL语句中的CASE
- COALESCE and CASE in SQL
- sql中的case函数
- sql 语句中的CASE 用法
- Sql sever中的CASE语句
- SQL中的CASE WHEN语句
- SQL中的CASE WHEN语句
- sql中的coalesce
- sql 中的 coalesce
- oracle 中的COALESCE函数
- COALESCE与ISNULL函数
- SQL中的NULL函数:isnull、ifnull、nullif、coalesce
- SQL语句中CASE函数的用法
- Server EventId 自动记录到 Oracle 中
- 线程处理完成,如何立即释放线程?
- mysql常用命令总结
- 是String,StringBuffer还是StringBuilder?
- 利用GridView显示主细表并添加删除、打开、关闭功能(转自孟子E章)
- SQL中的coalesce函数与case语句
- 贴一个干活用的Shell(关于oracle的,分享)
- Client:TSocketConnection 和Server: Scktsrvr关系----压缩数据传输方案 - cheng_ds的专栏 - CSDNBlog
- 让web应用实现无刷新数据交互-基于webservice
- 谈系统优化
- 单选按钮组在 Firefox 中使用时可能出现的一种Bug
- 为你的数据库做好数据字典
- Understanding .net CLR garbage collection
- MyEclipse Hibernate开发——自动生成 POJO、DAO
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;