请问这个SQL如何写？

表A：
c1 c2 c3  
1 2 3
11 22 33 
表B:
c4 c5
4 5
44 55
如何让表A表B一行对一行组成一个查询结果呢？

比如：

c1 c2 c3 c4 c5 
1 2 3 4 5
11 22 33 44 55

通过什么方式？

没有主键的情况下。

如果要通过主键相等？

怎么增加主键

表数据非常大。

SQL code

--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 

很好

SQL code

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 行受影响)
*/

SQL code

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 行受影响)
*/

SQL code

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 行受影响)
*/