SQL 多表合并查询 要求将多表的列合并为一个表的列

xzm71908 2015-10-18 09:09:39


如图所示,有多个表(图中只是为了演示查了2个表),这多个表有一个共同的字段(外键)
问题:
1、如何将多个表的数据合并到一个表,每个表的列都要求查询出来
2、合并后的新表效果如图,假如A表当中 有5条数据,B表当中只有1条数据,那么B表剩余没有数据的行要求显示 null
...全文
119 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
frankl123 2015-10-19
用left join 就可以吧
回复
卖水果的net 2015-10-19

-- 大概这个样子。
create table a(id int , name varchar(30))
go
insert into a select top 10 id , name from sysobjects 
go
create table b (id int , name varchar(30))
go
insert into b select top 5 id,name 
from sysobjects 
where id not in (select id from a)
go
create table c (id int , name varchar(30))
go
insert into c 
select top 6 id,name 
from sysobjects 
where id not in (select id from a) and id not in(select id from b)
go
select * from a 
go
select * from b 
go
select * from c 
go
with ta as (
select row_number() over(order by id) rn , * from a 
),
tb as (
select row_number() over(order by id) rn , * from b
),
tc as (
select row_number() over(order by id) rn , * from c
)
select ta.id , ta.name ,tb.id , tb.name , tc.id , tc.name
from ta 
full join tb on ta.rn = tb.rn
full join tc on coalesce(ta.rn,tb.rn) = tc.rn
go
drop table a ,b ,c
go


(10 行受影响)

(5 行受影响)

(6 行受影响)
id          name
----------- ------------------------------
3           sysrscols
5           sysrowsets
7           sysallocunits
8           sysfiles1
17          syspriorities
19          sysfgfrag
23          sysphfg
24          sysprufiles
25          sysftinds
27          sysowners

(10 行受影响)

id          name
----------- ------------------------------
29          sysprivs
34          sysschobjs
41          syscolpars
44          sysnsobjs
46          syscerts

(5 行受影响)

id          name
----------- ------------------------------
49          sysxprops
50          sysscalartypes
51          systypedsubobjs
54          sysidxstats
55          sysiscols
58          sysbinobjs

(6 行受影响)

id          name                           id          name                           id          name
----------- ------------------------------ ----------- ------------------------------ ----------- ------------------------------
3           sysrscols                      29          sysprivs                       49          sysxprops
5           sysrowsets                     34          sysschobjs                     50          sysscalartypes
7           sysallocunits                  41          syscolpars                     51          systypedsubobjs
8           sysfiles1                      44          sysnsobjs                      54          sysidxstats
17          syspriorities                  46          syscerts                       55          sysiscols
19          sysfgfrag                      NULL        NULL                           58          sysbinobjs
23          sysphfg                        NULL        NULL                           NULL        NULL
24          sysprufiles                    NULL        NULL                           NULL        NULL
25          sysftinds                      NULL        NULL                           NULL        NULL
27          sysowners                      NULL        NULL                           NULL        NULL

(10 行受影响)



回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-10-18 09:09
社区公告
暂无公告