SQL里如何才能同字段选择匹配 第2页

--> 测试数据：[A表]
go
if object_id('[A表]') is not null
drop table [A表]
go
create table [A表](
[id] int,
[name] varchar(6),
[a_sn] varchar(10),
[b_sn] varchar(10),
[c_sn] varchar(10)
)
go
insert [A表]
select 1,'打鱼','1010201','1010200',null union all
select 2,'农家乐','1010301','1010300','1020401' union all
select 3,'打台球','1020402','1020400','1010200'

select *from [A表]
--> 测试数据：[B表]
go
if object_id('[B表]') is not null
drop table [B表]
go
create table [B表](
[a_sn] varchar(10),
[b_sn] varchar(10),
[a_name] varchar(10),
[b_name] varchar(6))
go
insert [B表]
select '1010201','1010200','经管系一班','经管系' union all
select '1010202','1010200','经管系二班','经管系' union all
select '1010301','1010300','通控系一班','通控系' union all
select '1010302','1010300','通控系二班','通控系' union all
select '1020401','1020400','数学系一班','数学系' union all
select '1020402','1020400','数学系二班','数学系'

select distinct [id],[name],d.[a_name],[b_name],e.[a_name] as [c_name]
from(
select distinct [id],[name],[a_name],[b_name],
isnull([c_sn],a.[a_sn]) as [c_sn]
from [A表]
left join
(select [a_sn],[a_name] from [B表])a
on [A表].a_sn=a.a_sn
left join
(select [b_sn],[b_name] from [B表])b
on [A表].b_sn=b.b_sn)d
left join(
select [a_sn],[a_name] from [B表]
union all
select [b_sn],[b_name] from [B表])e
on d.[c_sn]=e.a_sn
/*
id    name    a_name    b_name    c_name
1    打鱼    经管系一班    经管系    经管系一班
2    农家乐    通控系一班    通控系    数学系一班
3    打台球    数学系二班    数学系    经管系
*/

上一页  [1] [2]