求一条三表连接的sql语句

lilyfirstone 2003-05-20 01:52:17
要求 ,有三个条,每个表有两列,有一个共同列name.
A表
name sex
bb 男
cc 女
B表
name addr
bb 无
ee 有
C表
name mail
ee @kk
gg @343

想要的就是
bb 男 无
cc 女
ee 有 @kk
gg @342

不要告诉我用别的方法,我就是想用一条sql语句实现
...全文
207 11 点赞 打赏 收藏 举报
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
caiyunxia 2003-05-20
create table #a(name varchar(10),sex varchar(10))
create table #b(name varchar(10),addr varchar(10))
create table #c(name varchar(10),mail varchar(10))
insert into #a(name,sex) values('bb','男')
insert into #a(name,sex) values('cc','女')
insert into #b(name,addr) values('bb','无')
insert into #b(name,addr) values('ee','有')
insert into #c(name,mail) values('ee','@kk')
insert into #c(name,mail) values('gg','@343')

select d.name,sex,addr,mail
from (select distinct name from #a union select distinct name from #b union select distinct name from #c ) d left join #a on #a.name=d.name
left join #b on #b.name = d.name left join #c on #c.name=d.name

select COALESCE (#a.name,#b.name,#c.name) as name ,sex,addr,mail
from #a full join #b on #b.name = #a.name full join #c on (#c.name=#b.name or #c.name=#a.name)

drop table #a
drop table #b
drop table #c


name sex addr mail
---------- ---------- ---------- ----------
bb 男 无 NULL
cc 女 NULL NULL
ee NULL 有 @kk
gg NULL NULL @343

(所影响的行数为 4 行)

name sex addr mail
---------- ---------- ---------- ----------
ee NULL 有 @kk
gg NULL NULL @343
bb 男 无 NULL
cc 女 NULL NULL
  • 打赏
  • 举报
回复
happydreamer 2003-05-20

declare @a table(a int,b int)
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)

--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右:
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外:
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b


cross join 是笛卡儿乘积 就是一张表的行数乘以另一张表的行数
left join 第一张表的连接列在第二张表中没有匹配是,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有匹配是,第一张表中的值返回null
full join 返回两张表中的行 left join+right join
inner join 只返回两张表连接列的匹配项
  • 打赏
  • 举报
回复
lilyfirstone 2003-05-20
谢谢大家捧场,我自以为sql语句没那么糟,现在才发现这样的问题就是给大家送分的
多谢多谢,又学了一招,以后自己也要多多努力啦

写完这个存储过程,结帖.因为诸位的语句,我还没空试,不知道是不是能有理想的效果.

不管怎么说,感激涕零,无以为表!
  • 打赏
  • 举报
回复
yoki 2003-05-20
select isnull(t.name,c.name) as name,t.sex,t.addr,c.mail
from
(select isnull(a.name,b.name) as name ,a.sex,b.addr
from a full join b on a.name=b.name)t
full join c on t.name=c.name
  • 打赏
  • 举报
回复
yoki 2003-05-20
select isnull(t.name,c.name) as name,xt.sex,t.addr,c.mail
from
(select isnull(a.name,b.name) as name ,a.sex,b.addr
from a full join b on a.name=b.name)t
full join c on t.name=c.name
  • 打赏
  • 举报
回复
select isnull(A.name, isnull(B.name, C.name)) name, A.sex, B.addr, C.mail
from A full join B on A.name = B.name
full join C on A.name = C.name
order by isnull(A.name, isnull(B.name, C.name))
  • 打赏
  • 举报
回复
firetoucher 2003-05-20
select D.name,sex,addr,mail
from
(select distinct name from A union select distinct name from B union select distinct name from C ) D,A,B,C
where D.name *= A.name and
D.name*= B.name and
D.name*= C.name
  • 打赏
  • 举报
回复
psxfghost 2003-05-20
唉,来晚了!................
^_^
  • 打赏
  • 举报
回复
Yang_ 2003-05-20
full join :

select isnull(x.name,c.name) as name,x.sex,x.addr,c.mail
from
(select isnull(a.name,b.name) as name ,a.sex,b.addr
from #a a full join #b b on a.name=b.name) x
full join #c c on x.name=c.name
  • 打赏
  • 举报
回复
caiyunxia 2003-05-20
select d.name,sex,addr,mail
from (select distinct name from A union select distinct name from b union select distinct name from c ) d left join a on a.name=d.name
left join b on b.name = d.name left join c on c.name=d.name
  • 打赏
  • 举报
回复
caiyunxia 2003-05-20
select d.name,sex,addr,mail
from (select distinct name from A union select distinct name from b union select distinct name from A ) d left join a on a.name=d.name
left join b on b.name = d.name left join c on c.name=d.name
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-20 01:52
社区公告
暂无公告