请教联表查询SQL语句
来源:百度知道 编辑:UC知道 时间:2024/06/12 07:53:57
a表:
aid bid1 bid2 name1 name2
1 2 4
2 3 2
b表
bid name
1 aa
2 bk
3 78
4 ee
a表的bid1和bid2对应b表的bid,请问怎么根据a表的bid1,bid2查出b表对应的name放到a表的左边 name1 ,name2 ?先谢谢啦。
select a.aid,a.bid1,a.bid2,b.name as name1,c.name as name2
from a表 a left join b表 b on a.bid1=b.bid
left join b表 c on a.bid2=c.bid
呵呵 ……
期待高手咯
cursor rec in (select a.*.b.* from a,b
where a.bid1=b.bid
or a.bid2=b.bid ) loop
update a set a.name1 = rec.name
where a.aid = rec.aid
and a.bid1 = rec.bid1;
update a set a.name2 = rec.name
where a.aid = rec.aid
and a.bid2 = rec.bid2;
end loop;
绝对能用:
select a.aid,a.bid1,a.bid2,b.name from a ,b where a.bid1=b.bid or a.bid2=b.bid
以下是我建的表:
create table b(bid int identity(1,1)primary key , name nvarchar(20))
insert b values('aa')
insert b values('bk')
insert b values('78')
insert b values('ee')
create table a(aid int identity(1,1)primary key , bid1 int, bid2 int , name1 nvarchar(20), name2 nvarc