>

值替换为钦点值的三种艺术共享,关键字在值为

- 编辑:澳门新葡亰平台游戏 -

值替换为钦点值的三种艺术共享,关键字在值为

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

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

摘要:

摘要:
下文通过案例分析in 关键字在值为null的利用比如,
解析出not in关键字在null值发生的那多少个音讯
日常来讲所示:

下文将享受三种将字段中null值替换为钦定值的办法分享,如下所示:
试验情况:sqlserver 二零零六 奥迪Q52

CREATE TABLE testA(keyId INT)
CREATE TABLE testB(keyId INT)
GO
INSERT INTO testA(keyId) VALUES (1)
INSERT INTO testA(keyId) VALUES (2)
INSERT INTO testA(keyId) VALUES (3)
GO
INSERT INTO testB(keyId) VALUES (1)
INSERT INTO testB(keyId) VALUES (2)
INSERT INTO testB(keyId) VALUES (4)
INSERT INTO testB(keyId) VALUES (NULL)
GO
 SELECT * FROM testA WHERE keyId IN (SELECT keyId FROM testB)
-----输出------
/*
keyId
1
2
*/
 SELECT * FROM testA WHERE keyId not IN (SELECT keyId FROM testB)
------无输出记录-----
/*
keyId
*/
INSERT INTO testA VALUES (NULL) ---在testA表中插入空值
SELECT * FROM testA WHERE EXISTS(
    SELECT null  FROM testB WHERE testA.keyId=testB.keyId    
)
----输出----
/*
keyId
1
2
*/
SELECT * FROM testA WHERE not  EXISTS(
    SELECT null  FROM testB WHERE testA.keyId=testB.keyId    
)
/*
keyId
3
NULL
*/
SELECT * FROM testA WHERE testA.keyId NOT in (
    SELECT testB.keyId FROM testB WHERE testB.keyId IS NOT NULL    
)

drop table testA
drop table testB

 

例: 

因而以上测量试验,大家能够见见not in 查询关键字假如子表中设有空值,则无从查询出另外记录,会促成万分爆发,
需利用not exists获取相应的空值信息

create table test(keyId int identity, info varchar(30))
go
insert into test(info)values('a'),('b'),(null),('d')
go 
---方法1:使用isnull替换
select keyId,isnull(info,'替换null值') as info from test 
go 
---方法2:使用case when 替换
select keyId,case when info is null then '替换null值' else info end as info from test 
---方法3:使用coalesce替换相应的值
select keyId , coalesce(info,'替换null值') as info from test 

go 
truncate table test 
drop table test  

 

本文由数据库发布,转载请注明来源:值替换为钦点值的三种艺术共享,关键字在值为