日期:2014-05-18 浏览次数:20552 次
--sql 2000用子查询 select m.c1,m.c2,m.c3,n.c4,n.c5 from (select t.* , px = (select count(1) from a where c1 < t.c1 or (c1 = t.c1 and c2 < t.c2) or (c1 = t.c1 and c2 = t.c2 and c3 < t.c3)) + 1 from a t) m full join (select t.* , px = (select count(1) from b where c4 < t.c4 or (c4 = t.c4 and c5 < t.c5)) + 1 from b t) n on m.px = n.px --sql 2005用row_number select m.c1,m.c2,m.c3,n.c4,n.c5 from (select t.* , px = row_number() over(order by c1 , c2 , c3) from a t) m full join (select t.* , px = row_number() over(order by c4 , c5) from b t) n on m.px = n.px
------解决方案--------------------
--如果不能通过字段区分出行的先后顺序,上面的sql 2005没有问题,sql 2000需要使用临时表。如下:
select id=identity(int,1,1),* into a_tmp from a
select id=identity(int,1,1),* into b_tmp from b
select m.c1,m.c2,m.c3,n.c4,n.c5 from
a_tmp m
full join
b_tmp n
on m.px = n.px
------解决方案--------------------
if object_id('A') is not null drop table A go create table A ( c1 int, c2 int, c3 int ) go insert into A select 1,2,3 union all select 11,22,33 go if object_id('B') is not null drop table B go create table B ( c4 int, c5 int ) go insert into B select 4,5 union all select 44,55 go select c1,c2,c3,c4,c5 from (select row=row_number() over(order by getdate()),* from A) t1 cross apply (select * from (select row=row_number() over(order by getdate()),* from B) t2 where t1.row=t2.row) t go /* c1 c2 c3 c4 c5 ----------- ----------- ----------- ----------- ----------- 1 2 3 4 5 11 22 33 44 55 (2 行受影响) */
------解决方案--------------------
if object_id('A') is not null drop table A go create table A ( c1 int, c2 int, c3 int ) go insert into A select 1,2,3 union all select 11,22,33 go if object_id('B') is not null drop table B go create table B ( c4 int, c5 int ) go insert into B select 4,5 union all select 44,55 go select c1,c2,c3,c4,c5 from (select row=row_number() over(order by getdate()),* from A) t1 cross apply (select * from (select row=row_number() over(order by getdate()),* from B) t2 where t1.row=t2.row) t go /* c1 c2 c3 c4 c5 ----------- ----------- ----------- ----------- ----------- 1 2 3 4 5 11 22 33 44 55 (2 行受影响) */