34,594
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity(1,1),Name1 varchar(10),Name2 varchar(10))
go
insert into tb
select 'N1',NULL union all
select 'N2',NULL union all
select NULL,'N3' union all
select 'N4',NULL
go
select isnull(Name1,Name2) from tb
select id,isnull(name1,name2) as [Name]
from tb
if object_id('tempdb..#') is not null drop table #
go
create table #(
ID INT,
NAME1 nVARCHAR(20),
NAME2 nVARCHAR(50))
insert # select 1,'N1',NULL
insert # select 2,'N2',NULL
insert # select 3,NULL,'N3'
insert # select 4,'N4',NULL
select ID,NAME1 from # where NAME1 is not NULL union all
select ID,NAME2 FROM # WHERE NAME1 is NULL order by ID
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Name1] varchar(2),[Name2] varchar(2))
insert [tb]
select 1,'N1',null union all
select 2,'N2',null union all
select 3,null,'N3' union all
select 4,'N4',null
---查询---
select
id,
isnull(name1,name2) as [Name]
from tb
---结果---
id Name
----------- ----
1 N1
2 N2
3 N3
4 N4
(所影响的行数为 4 行)
if object_id('tempdb..#')is not null drop table #
go
create table #(ID int, Name1 varchar(10), Name2 varchar(10))
insert # select 1 ,'N1' ,NULL
insert # select 2 , 'N2' , NULL
insert # select 3 , NULL , 'N3'
insert # select 4, 'N4', NULL
select id,[name] from (
select id,name1 [Name] from #
union all
select id,name2 from #)T
where [name]is not null
/*id name
----------- ----------
1 N1
2 N2
4 N4
3 N3*/