SQL小知识009:COALESCE()列表中所有项中将返回第一个非空值

例子：有一个Persons数据表，它有三个字段FirstName、MiddleName和LastName。

用一个字符串列出他们的全名

SELECT FirstName + '' '' +COALESCE(MiddleName,'''')+ '' '' +COALESCE(LastName,'''')

需要对多个字段进行非空判断，显示几个字段中不为空（最前边）的那个,字段少的时候CASE WHEN

如果有十几二十个字段就不好判断,可以使用COALESCE来做处理

if not object_id(N'Tempdb..#T1') is null 
drop table #T1
Go
Create table #T1([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)
Insert #T1 
select N'李四',N'语文',60 union all
select N'李四',N'数学',70 union all
select N'李四',N'英语',80
GO
select * from #T1
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)
Insert #T2
select N'张三',N'语文',90 union all
select N'张三',N'数学',80 union all
select N'张三',N'物理',70
GO
select * from #T2
if not object_id(N'Tempdb..#T3') is null
drop table #T3
Go
Create table #T3([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)
Insert #T3
select N'王五',N'语文',90 union all
select N'王五',N'数学',80 union all
select N'王五',N'历史',70
Go
select * from #T3

SELECT  COALESCE(#T1.Subject, #T2.Subject, #T3.Subject) AS Subject ,#T1.Name ,#T1.Score ,#T2.Name ,#T2.Score,#T3.Name ,#T3.Score
FROM  #T1 FULL JOIN #T2 ON #T2.Subject = #T1.Subject
FULL JOIN #T3 ON #T2.Subject = #T3.Subject