又是两表合并问题

jimo_lang 2010-09-20 11:41:22
表1
字段1,字段2
11,21
12,22
13,23


表2
字段1,字段2
a1,b1
a2,b2
a3,b3
a4,b4

合成
11,21,a1,b1
12,22,a2,b2
13,23,a3,b3
null,null,a4,b4


意思是两表就是左右合并起来,不用对应
...全文
54 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
就是just4 2010-09-20
DECLARE @test1 TABLE(f1 INT,f2 int)
DECLARE @test2 TABLE(s1 VARCHAR(20),s2 VARCHAR(20))

INSERT INTO @test1(f1,f2)
SELECT 11,21 UNION ALL
SELECT 12,22 UNION ALL
SELECT 13,23

INSERT INTO @test2(s1,s2)
SELECT 'a1','b1' UNION ALL
SELECT 'a2','b2' UNION ALL
SELECT 'a3','b3' UNION ALL
SELECT 'a4','b4'

IF OBJECT_ID('tempdb..#1') IS NOT NULL DROP TABLE tempdb..#1
SELECT IDENTITY(INT,1,1) id,t1.f1,t1.f2 INTO #1 FROM @test1 AS t1
IF OBJECT_ID('tempdb..#2') IS NOT NULL DROP TABLE tempdb..#2
SELECT IDENTITY(INT,1,1) id,t2.s1,t2.s2 INTO #2 FROM @test2 AS t2

SELECT t1.f1,t1.f2,t2.s1,t2.s2 FROM #1 AS t1 FULL OUTER JOIN #2 AS t2 ON t1.id = t2.id

DROP TABLE tempdb..#1
DROP TABLE tempdb..#2
/*
f1 f2 s1 s2
----------- ----------- -------------------- --------------------
11 21 a1 b1
12 22 a2 b2
13 23 a3 b3
NULL NULL a4 b4
*/
回复
ws_hgo 2010-09-20

if object_id('表1') is not null drop table 表1
create table 表1
(
字段1 int,
字段2 int
)
insert into 表1 select 11,21
union all select 12,22
union all select 13,23

if object_id('表2') is not null drop table 表2
create table 表2
(
字段1 varchar(20),
字段2 varchar(20)
)
insert into 表2 select 'a1','b1'
union all select 'a2','b2'
union all select 'a3','b3'
union all select 'a4','b4'

select t.字段1,t.字段2,t1.字段1,t1.字段2
from
(
select *,
(select count(*)+1 from 表1 where 字段1<t.字段1) row from 表1 t
)
t
full join
(
select *,
(select count(*)+1 from 表2 where 字段1<t.字段1) row from 表2 t
) t1
on t.row=t1.row

字段1 字段2 字段1 字段2
----------- ----------- -------------------- --------------------
11 21 a1 b1
12 22 a2 b2
13 23 a3 b3
NULL NULL a4 b4

(4 行受影响)
回复
zsh0809 2010-09-20
try~
select a.* ,row_number()over(order by getdate()) row
from a
full join
(
select b.* ,row_number()over(order by getdate()) row
from b) m
on m.row=a.row
回复
喜-喜 2010-09-20
use test
go
if object_id('test.dbo.表1') is not null drop table 表1
-- 创建数据表
create table 表1
(
字段1 char(3),
字段2 char(3)
)
go
--插入测试数据
insert into 表1 select '11','21'
union all select '12','22'
union all select '13','23'
go
if object_id('test.dbo.表2') is not null drop table 表2
-- 创建数据表
create table 表2
(
字段1 char(3),
字段2 char(3)
)
go
--插入测试数据
insert into 表2 select 'a1','b1'
union all select 'a2','b2'
union all select 'a3','b3'
union all select 'a4','b4'
go
--代码实现

select a.字段1,a.字段2,b.字段1,b.字段2
from (select idd=row_number()over(order by getdate()),* from 表1)a
full join (select idd=row_number()over(order by getdate()),* from 表2)b on a.idd=b.idd


/*测试结果

字段1 字段2 字段1 字段2
---------------------
11 21 a1 b1
12 22 a2 b2
13 23 a3 b3
NULL NULL a4 b4

(4 行受影响)
*/
回复
dawugui 2010-09-20
--sql 2005
select m.字段1,m.字段2,n.字段1,n.字段2 from
(select t.* ,px=row_number() over(order by 字段1,字段2) from t1 t) m
full join
(select t.* ,px=row_number() over(order by 字段1,字段2) from t2 t) n
on m.px = n.px
回复
dawugui 2010-09-20
--sql 2000
select m.字段1,m.字段2,n.字段1,n.字段2 from
(select t.* ,px=(select count(1) from t1 where 字段1 < t.字段1 or (字段1 = t.字段1 and 字段2<t.字段2)) + 1 from t1 t) m
full join
(select t.* ,px=(select count(1) from t2 where 字段1 < t.字段1 or (字段1 = t.字段1 and 字段2<t.字段2)) + 1 from t2 t) n
on m.px = n.px
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

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