如何连接,请教了。表A
Aid Astr AB AC AD
1 苹果 1 1 1表B
Bid Bstr
1 香蕉表C
Cid Cstr
1 橘子表D
Did Dstr
1 桃子想用AB,AC,AD分别与Bid,Cid,Did 链接,最后输出结果是Aid Astr AB AC AD
1 苹果 香蕉 橘子 桃子刚开始学,这个问题纠结很久了,求解答
Aid Astr AB AC AD
1 苹果 1 1 1表B
Bid Bstr
1 香蕉表C
Cid Cstr
1 橘子表D
Did Dstr
1 桃子想用AB,AC,AD分别与Bid,Cid,Did 链接,最后输出结果是Aid Astr AB AC AD
1 苹果 香蕉 橘子 桃子刚开始学,这个问题纠结很久了,求解答
select a.aid,a.astr,b.bstr,c.cstr,d.dstr
from a join b on a.ab = b.bid
join c on a.ac = c.cid
join d on a.ad = d.did
--用inner join 内联接
if object_id('a') is not null
drop table a
go
create table a
(
aid int,
astr varchar(10),
ab int,
ac int,
ad int
)
go
insert into a select 1,'苹果',1,1,1
go
if object_id('b') is not null
drop table b
go
create table b
(
bid int,
bstr varchar(10)
)
go
insert into b select 1,'香蕉'
go
if object_id('c') is not null
drop table c
go
create table c
(
cid int,
cstr varchar(10)
)
go
insert into c select 1,'橘子'
go
if object_id('d') is not null
drop table d
go
create table d
(
did int,
dstr varchar(10)
)
go
insert into d select 1,'桃子'
go
select aid,astr,bstr,cstr,dstr
from a inner join b on a.ab=b.bid
inner join c on a.ac=c.cid
inner join d on a.ad=d.did
go
/*
aid astr bstr cstr dstr
----------- ---------- ---------- ---------- ----------
1 苹果 香蕉 橘子 桃子(1 行受影响)*/
--改一下列名
if object_id('a') is not null
drop table a
go
create table a
(
aid int,
astr varchar(10),
ab int,
ac int,
ad int
)
go
insert into a select 1,'苹果',1,1,1
go
if object_id('b') is not null
drop table b
go
create table b
(
bid int,
bstr varchar(10)
)
go
insert into b select 1,'香蕉'
go
if object_id('c') is not null
drop table c
go
create table c
(
cid int,
cstr varchar(10)
)
go
insert into c select 1,'橘子'
go
if object_id('d') is not null
drop table d
go
create table d
(
did int,
dstr varchar(10)
)
go
insert into d select 1,'桃子'
go
select aid,astr,ab=bstr,ac=cstr,ad=dstr
from a inner join b on a.ab=b.bid
inner join c on a.ac=c.cid
inner join d on a.ad=d.did
go
/*
aid astr ab ac ad
----------- ---------- ---------- ---------- ----------
1 苹果 香蕉 橘子 桃子(1 行受影响)
*/