求一句外连接SQL语句

[bigmingming]

表A
ID Name Type
1 AA 是
2 AA 否
...

字典表B

NAME FULLNAME
AA 测试A

字典表C
NAME FULLNAME
AA 测试B

当TYPE为“是”时，left outer join连接B表，
当TYPE为“否”时，left outer join连接C表

结果
ID Name Type FULLNAME
1 AA 是 测试A
2 AA 否 测试B

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([ID] int,[Name] nvarchar(22),[Type] nvarchar(21))
Insert #A
select 1,N'AA',N'是' union all
select 2,N'AA',N'否'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([Name] nvarchar(22),[FullName] nvarchar(23))
Insert #B
select N'AA',N'测试A'
GO
if not object_id(N'Tempdb..#C') is null
	drop table #C
Go
Create table #C([Name] nvarchar(22),[FullName] nvarchar(23))
Insert #C
select N'AA',N'测试B'
Go
--测试数据结束
SELECT  #A.* ,
        CASE WHEN Type='是' THEN #B.FullName
             ELSE #C.FullName
        END AS FullName
FROM    #A
        LEFT JOIN #B ON #B.Name = #A.Name
        LEFT JOIN #C ON #C.Name = #A.Name

[James__z]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([ID] int,[Name] nvarchar(22),[Type] nvarchar(21))
Insert #A
select 1,N'AA',N'是' union all
select 2,N'AA',N'否'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([Name] nvarchar(22),[FullName] nvarchar(23))
Insert #B
select N'AA',N'测试A'
GO
if not object_id(N'Tempdb..#C') is null
	drop table #C
Go
Create table #C([Name] nvarchar(22),[FullName] nvarchar(23))
Insert #C
select N'AA',N'测试B'
Go
--测试数据结束
SELECT  #A.* ,
        CASE WHEN Type='是' THEN #B.FullName
             ELSE #C.FullName
        END AS FullName
FROM    #A
        LEFT JOIN #B ON #B.Name = #A.Name
        LEFT JOIN #C ON #C.Name = #A.Name

我觉得版主真的太厉害了，分分钟搞定这些问题！！！

[砸死牛顿的苹果]

引用1楼数据

if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([ID] int,[Name] nvarchar(22),[Type] nvarchar(21))
Insert #A
select 1,N'AA',N'是' union all
select 2,N'AA',N'否'
GO
if not object_id(N'Tempdb..#B') is null
    drop table #B
Go
Create table #B([Name] nvarchar(22),[FullName] nvarchar(23))
Insert #B
select N'AA',N'测试A'
GO
if not object_id(N'Tempdb..#C') is null
    drop table #C
Go
Create table #C([Name] nvarchar(22),[FullName] nvarchar(23))
Insert #C
select N'AA',N'测试B'
Go

select A.*,t.FullName from #a a
left join	
	(select *,'是' as tp from #b
	union all
	select *,'否' as tp from #c
	)t
on a.Name =t.Name and a.Type =t.tp

ID	Name	Type	FullName
1	AA	是	测试A
2	AA	否	测试B

[删库到跑路]

这里的人个个都是人才，说话又好听，我超喜欢这里的