表结构和数据如下:
ID Name1 Name2
1 N1 NULL
2 N2 NULL
3 NULL N3
4 N4 NULL我想最终显示的时候仍然是
1 N1
2 N2
3 N3
4 N4
请问这样的SQL语句该如何写?谢谢!
ID Name1 Name2
1 N1 NULL
2 N2 NULL
3 NULL N3
4 N4 NULL我想最终显示的时候仍然是
1 N1
2 N2
3 N3
4 N4
请问这样的SQL语句该如何写?谢谢!
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*/
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 行)
如果Name1不为空去Name1,否则去Name2
select ID,CASE Name1 WHEN NOT NULL THEN NAME1 ELSE NAME2 END AS NAME FROM 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
from table
select id,isnull(name1,name2) as [Name]
from tb
select id,isnull(name1,name2) as [Name]
from tb
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
所以如果两个字段都有值,肯定返回前者表达式的值。