表A
yuangong
字段
ygid ming
1 张
2 王
3 李表B
liushuilsid ygid1 ygid2 ygid3
1 1 1 0
2 1 1 0
3 2 3 0好了,我想读取表B但是让它显示ygid1 ygid2 ygid3时自动在表A中查找分别返回表A的ming这个字段也就是说,显示在电脑上的不是123而是根据ygid分别显示"张王李",这个sql语句就是将a表和b表相互嵌套,看似简单,但就不知道怎么写,请老师指点一二
yuangong
字段
ygid ming
1 张
2 王
3 李表B
liushuilsid ygid1 ygid2 ygid3
1 1 1 0
2 1 1 0
3 2 3 0好了,我想读取表B但是让它显示ygid1 ygid2 ygid3时自动在表A中查找分别返回表A的ming这个字段也就是说,显示在电脑上的不是123而是根据ygid分别显示"张王李",这个sql语句就是将a表和b表相互嵌套,看似简单,但就不知道怎么写,请老师指点一二
declare @yuangong table([ygid] int,[ming] nvarchar(1))
Insert @yuangong
select 1,N'张' union all
select 2,N'王' union all
select 3,N'李'declare @liushui table([lsid] int,[ygid1] int,[ygid2] int,[ygid3] int)
Insert @liushui
select 1,1,1,0 union all
select 2,1,1,0 union all
select 3,2,3,0
Select a.[lsid],b.ming,c.ming,d.ming
from @liushui as a
left join @yuangong as b on a.ygid1=b.ygid
left join @yuangong as c on a.ygid2=c.ygid
left join @yuangong as d on a.ygid3=d.ygid
/*
lsid ming ming ming
1 张 张 NULL
2 张 张 NULL
3 王 李 NULL
*/
from liushui a
left join yuangong b on a.ygid1=b.ygid
left join yuangong c on a.ygid2=b.ygid
left join yuangong d on a.ygid3=d.ygid当然,也可以这样写select lsid,(select ming from yuangong where ygid=a.ygid1) ygid1,
(select ming from yuangong where ygid=a.ygid2) ygid2,(select ming from yuangong where ygid=a.ygid3) ygid3 from liushui a