有三个表:
bkind , skind, otherbkind:
id cid cname
1 72 aa
2 35 bb
----------------------
skindid bid bname sid sname
1 35 bb 36 mm
2 35 bb 37 mm1
-----------------------
otherid bid sid
1 72 -1
2 35 36
3 35 37怎样查询出
1 aa
2 bb mm
3 bb mm1
bkind , skind, otherbkind:
id cid cname
1 72 aa
2 35 bb
----------------------
skindid bid bname sid sname
1 35 bb 36 mm
2 35 bb 37 mm1
-----------------------
otherid bid sid
1 72 -1
2 35 36
3 35 37怎样查询出
1 aa
2 bb mm
3 bb mm1
from bkind full join skind on skind.bname =bkind.cname
create table bkind
(id int, cid int, cname varchar(4))
insert into bkind select 1, 72, 'aa'
union all select 2, 35, 'bb'create table skind
(id int, bid int, bname varchar(4),sid int,sname varchar(5))
insert into skind select 1, 35, 'bb', 36, 'mm'
union all select 2, 35, 'bb', 37, 'mm1'create table other
(id int, bid int, sid int)
insert into other select 1, 72, -1
union all select 2, 35, 36
union all select 3, 35, 37select a.id,b.cname,c.sname from other a
left join bkind b
on b.cid=a.bid
left join skind c
on c.bname=b.cname and c.sid=a.sidid cname sname
----------- ----- -----
1 aa NULL
2 bb mm
3 bb mm1(所影响的行数为 3 行)
go
create table skind(id int, bid int, bname varchar(20), sid int,sname varchar(20))
go
insert into bkind select 1,72,'aa'
union select 2,35,'bb'go
insert into skind select 1,35,'bb',36,'mm'
union select 2,35,'bb',37,'mm1'
goselect identity(int ,1,1) id,isNull(bkind.cname,skind.bname) name,skind.sname into #t
from bkind left join skind on skind.bname =bkind.cname
go
select * from #tdrop table bkind
drop table skind
drop table #t
from bkind inner join other on bkind.cid=other.bid
left join skind on bkind.cid=skind.bid
from other as o join bkind as b on o.bid=b.cid
left join s on o.bid=s.bid and o.sid=s.sid
from other as o join bkind as b on o.bid=b.cid
left join skind as s on o.bid=s.bid and o.sid=s.sid