美高梅4858官方网站coalesce系统函数简介,Server函
分类:美高梅-数据

转自:http://www.maomao365.com/?p=4390

利用nullif函数

coalesce( expression [ ,...n ] )返回其参数中第一个非空表达式。

下表列出 SET 与 SELECT 的区别

一、coalesce函数简介

nullif函数有两个参数,定义如下:

 

  SELECT SET
同时对多个变量同时赋值时 支持 不支持
表达式返回多个值时 将返回的最后一个值赋给变量 出错
表达式未返回值时 变量保持原值 变量被赋null值

coalesce 系统函数,比ISNULL更强大,更方便的系统函数,
coalesce可以接收多个参数,返回最左边不为NULL的参数,当所有参数都为空时,则返回NULL
coalesce是最优isnull写法解决方案
以前我们使用isnull对两列或多列数据进行为空返回时候,需要多次使用isnull函数
—————————————————————————-
例:
declare @a varchar(10),@b varchar(10),@c varchar(10)
当@a为null时,我们查看@b是否为NULL,不为null,则返回@b ,否则查看@c 不为NULL,则返回@c ,否则返回NULL

NULLIF( expression , expression )

Select coalesce(null,null,'1','2') //结果为 1

(1).同时对多个变量同时赋值时

select isnull(@a,isnull(@b,isnull(@c,null)))
/*当需判断的参数越多时,我们的函数表达式就会变的异常复杂*/

其作用就是:如果两个指定的表达式相等,就返回null值。

coalesce(expression1**,**...n) 与此 case函数等效:

declare @a varchar(128), @b varchar(128) SET @a='ABC',@b='EFG' GO --报错:消息 102,级别 15,状态 1,第 3 行 ',' 附近有语法错误。

但我们使用coalesce函数,会使此 表达式变的优美,通俗易懂
select coalesce(@a,@b,@c)
——————————————————————————–

看一示例:

 

declare @a varchar(128), @b varchar(128) SELECT @a='ABC',@b='EFG' GO --正确运行


select nullif(0,0)

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END

(2).表达式返回多个值时

二、coalesce 应用举例

运行结果:null

 

在说明这一项前我们先创建一个要用到的表,并对其赋值,代码如下:

 

我们知道,null与任何数进行任何运算,其结果都等于null,利用这点,我们可以将上面的sql改写为:

注意:
当第一个表达式为字符串且不能转化为整数时,若在后面的表达式中有整数,这样的语句是会报错的。
例如:

 

  declare @a varchar(10),@b varchar(10),@c varchar(10),@d int 

 select coalesce(@a,@b,@c)

 set @a ='g'
 select coalesce(@a,@b,@c)

 set @a =null 
 set @b ='g2'
 set @c ='g3'
 select coalesce(@a,@b,@c)

 set @a =null
 set @b =null
 set @c =null 
 set @d =100

 select coalesce(@a,@b,@c,@d)

declare @a int;

Select COALESCE('a',null,'1',2) //这是错误的

美高梅4858官方网站 1美高梅4858官方网站 2赋值测试表--fuzhiTEST

 

declare @b int;

这相当于

美高梅4858官方网站 3

set @a=10

 

create table fuzhiTEST(
id int ,
name varchar(128)
)
go
insert into fuzhiTEST(id,name) values(1,'name1')
insert into fuzhiTEST(id,name) values(2,'name2')
insert into fuzhiTEST(id,name) values(3,'name3')
go

set @b=0

select 
CASE
WHEN ('a' IS NOT NULL) THEN 'a'
WHEN (2 IS NOT NULL) THEN 2
ELSE NULL
END //会出现错误,因为系统无法将a转换为相应的整数

美高梅4858官方网站 4

select @a/nullif(@b,0)

 

 

其运行结果自然为null了。

但是

declare @name varchar(128) set @name = (select name from fuzhiTEST) GO  --报错:消息 512,级别 16,状态 1,第 2 行        

再利用isnull函数,我们就可以实现当@b=0的时候,结果返回1的需求了。最终的sql改写如下:

 

子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。

declare @a int;

select 
CASE
WHEN ('a' IS NOT NULL) THEN 'a'
WHEN (2 IS NOT NULL) THEN '2'
ELSE NULL
END //这是正确的

declare @name varchar(128) select @name =name from fuzhiTEST print @name GO  --正确运行:显示 name3

declare @b int;

 

(3).表达式未返回值时

set @a=10

所以我们在使用coalesce函数时,应该相当小心。
isnull( check_expression , replacement_value ) 使用指定的替换值替换 NULL。
replacement_value 必须是可以隐式转换为 check_expresssion 类型的类型。
在表stu中
美高梅4858官方网站 5

declare @name varchar(128) set @name = 'SET初始值' set @name = (select name from fuzhiTEST where id = 4 ) print @name GO   --正确运行:显示NULL

set @b=0

select isnull(ssex,'p') from stu //若ssex列中有NULL值,那么返回p

declare @name varchar(128) set @name = 'SELECT初始值' select @name = name from fuzhiTEST where id = 4 print @name --正确运行:显示 SELECT初始值

select isnull(@a/nullif(@b,0),1)

nullif( expression , expression ) 如果两个指定的表达式相等,则返回空值,否则 NULLIF 返回第一个 expression 的值。

注意:SELECT 也可以将标量子查询的值赋给变量,如果标量子查询不返回值,则变量被置为 null 值(此时与使用 SET 赋值是完全相同的)。 对标量子查询的概念大家可能觉得陌生,下面举个例子说明下(对比上面绿色部分):

OK,两种方法介绍完毕,相对于方法一,我更推荐大家使用方法二,因为方法二的代码更简省。

select nullif(1,1) //结果为 NULL
select nullif(1,2) //结果为 1

declare @name varchar(128) set @name = 'SELECT初始值' select @name =(select name from fuzhiTEST where id = 4 ) print @name --正确运行:显示NULL

(如果有朋友对isnull函数不了解的话,这里可以解释一下。

来源

isnull的定义如下:

 

isnull(参数1,参数2)

 

其作用就是,当参数1等于null时,就返回参数2的值。)

本文由美高梅网站是多少发布于美高梅-数据,转载请注明出处:美高梅4858官方网站coalesce系统函数简介,Server函

上一篇:没有了 下一篇:MySQL高可用之PXC安装部署
猜你喜欢
热门排行
精彩图文