表A:
create table #A(A_ID int,姓名 varchar(10))
insert #A
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'
表B:
create table #B(B_id int,标题 varchar(10) ,申请人ID int, 审核人ID int,删除人ID int)
insert #B
select 1,'rr',1,2,3 union all
select 2,'gg',2,2,3 union all
select 3,'hh',3,3,1
select t2.B_id,t2.标题,申请人姓名=(select 姓名 from #A where A_ID=t2.申请人ID),
审核人姓名=(select 姓名 from #A where A_ID=t2.审核人ID),
删除人姓名=(select 姓名 from #A where A_ID=t2.删除人ID)
from #B t2
------------------
1 rr aa bb cc
2 gg bb bb cc
3 hh cc cc aa
select B_id,标题,(select 姓名 from A where a.A_ID = b.申请人ID ) as 申请人姓名,(select 姓名 from A where a.A_ID = b.审核人姓名) as 审核人姓名,(select 姓名 from A where a.A_ID = b.删除人姓名 ) as 删除人姓名 from b
select B_id,标题,a1.姓名 as 申请人姓名 ,a2.姓名 as 审核人姓名,a3.姓名 as 删除人姓名
from B
left join A a1
on a1.A_ID=B.申请人ID
left join A a2
on a2.A_ID=B.审核人ID
left join A a3
on a3.A_ID=B.删除人ID
函数方法
表A:
create table A(A_ID int,姓名 varchar(10))
insert A
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'
表B:
create table B(B_id int,标题 varchar(10) ,申请人ID int, 审核人ID int,删除人ID int)
insert B
select 1,'rr',1,2,3 union all
select 2,'gg',2,2,3 union all
select 3,'hh',3,3,1
create function fun_name(@id int)
returns varchar(10)
as
begin
declare @str varchar(10)
select @str=姓名 from A where A_ID=@id
return @str
end
select B_id , 标题 , dbo.fun_name(申请人ID) as 申请人姓名,dbo.fun_name(审核人ID) as 审核人姓名 ,dbo.fun_name(删除人ID) as 删除人姓名 from B
--------------------
1 rr aa bb cc
2 gg bb bb cc
3 hh cc cc aa