请教这个SQL怎么写

请问这个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) mfull 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) non m.px = n.px --sql 2005用row_numberselect 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) mfull join(select t.* , px = row_number() over(order by c4 , c5) from b t) non 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  

[解决办法]
探讨

表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
if object_id('A') is not null   drop table Agocreate table A( c1 int, c2 int, c3 int)goinsert into Aselect 1,2,3 union allselect 11,22,33goif object_id('B') is not null   drop table Bgocreate table B( c4 int, c5 int)goinsert into Bselect 4,5 union allselect 44,55goselect 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) tgo/*c1          c2          c3          c4          c5----------- ----------- ----------- ----------- -----------1           2           3           4           511          22          33          44          55(2 行受影响)*/
[解决办法]
SQL code
if object_id('A') is not null   drop table Agocreate table A( c1 int, c2 int, c3 int)goinsert into Aselect 1,2,3 union allselect 11,22,33goif object_id('B') is not null   drop table Bgocreate table B( c4 int, c5 int)goinsert into Bselect 4,5 union allselect 44,55goselect 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) tgo/*c1          c2          c3          c4          c5----------- ----------- ----------- ----------- -----------1           2           3           4           511          22          33          44          55(2 行受影响)*/