select a.col1,a.col2,b.col1,b.col2 from (select rn=row_number() over(order by getdate()),* from ta) a full join (select rn=row_number() over(order by getdate()),* from tb) b on a.rn=b.rn
rownum不行,两个结果集的数量可能会不一致,而且我用rownum作为连接条件,结果集显示顺序会变掉 结果集1 aa 11 bb 22 结果集2 cc 33 dd 44 ee 55想连起来,变成这样子 aa 11 cc 33 bb 22 dd 44 ee 55
create table t1 ( col1 varchar(10), col2 int, ) create table t2 ( col3 varchar(10), col4 int, ) insert into t1 values('aa',11) insert into t1 values('bb',22)insert into t2 values('cc',33) insert into t2 values('dd',44) insert into t2 values('ee',55) select a.col1,a.col2,b.col3,b.col4 from ( select row_number() over(order by getdate()) id,* from t1 ) a full join ( select row_number() over(order by getdate()) id,* from t2 ) b on a.id = b.id /* col1 col2 col3 col4 ---------- ----------- ---------- ----------- aa 11 cc 33 bb 22 dd 44 NULL NULL ee 55(3 行受影响)
create table t1(c1 varchar(10),c2 int) insert into t1 select 'aa',11 insert into t1 select 'bb',22 create table t2(c3 varchar(10),c4 int) insert into t2 select 'cc',33 insert into t2 select 'dd',44 insert into t2 select 'ee',55 go select b.c1,b.c2,c.c3,c.c4 from master..spt_values a left join ( select row_number()over(order by (select 1))rn,* from t1 )b on a.number=b.rn left join( select row_number()over(order by (select 1))rn,* from t2 )c on a.number=c.rn where a.type='p' and a.number>0 and not(a.number>b.rn and a.number>c.rn) /* c1 c2 c3 c4 ---------- ----------- ---------- ----------- aa 11 cc 33 bb 22 dd 44 NULL NULL ee 55(3 行受影响)*/ go drop table t1,t2
现在的问题是连能连上,但是用rownum做条件,显示的顺序会乱掉, aa 11 ee 55 bb 22 cc 33 dd 44 变成这样子了,在两个结果集语句中我有使用ORDER BY
a.col1,a.col2,b.col1,b.col2
from
(select rn=row_number() over(order by getdate()),* from ta) a
full join
(select rn=row_number() over(order by getdate()),* from tb) b
on
a.rn=b.rn
结果集1
aa 11
bb 22
结果集2
cc 33
dd 44
ee 55想连起来,变成这样子
aa 11 cc 33
bb 22 dd 44
ee 55
(
col1 varchar(10),
col2 int,
)
create table t2
(
col3 varchar(10),
col4 int,
)
insert into t1 values('aa',11)
insert into t1 values('bb',22)insert into t2 values('cc',33)
insert into t2 values('dd',44)
insert into t2 values('ee',55)
select a.col1,a.col2,b.col3,b.col4 from
(
select row_number() over(order by getdate()) id,* from t1
) a full join
(
select row_number() over(order by getdate()) id,* from t2
) b
on a.id = b.id
/*
col1 col2 col3 col4
---------- ----------- ---------- -----------
aa 11 cc 33
bb 22 dd 44
NULL NULL ee 55(3 行受影响)
insert into t1 select 'aa',11
insert into t1 select 'bb',22
create table t2(c3 varchar(10),c4 int)
insert into t2 select 'cc',33
insert into t2 select 'dd',44
insert into t2 select 'ee',55
go
select b.c1,b.c2,c.c3,c.c4
from master..spt_values a left join (
select row_number()over(order by (select 1))rn,* from t1
)b on a.number=b.rn left join(
select row_number()over(order by (select 1))rn,* from t2
)c on a.number=c.rn
where a.type='p' and a.number>0 and not(a.number>b.rn and a.number>c.rn)
/*
c1 c2 c3 c4
---------- ----------- ---------- -----------
aa 11 cc 33
bb 22 dd 44
NULL NULL ee 55(3 行受影响)*/
go
drop table t1,t2
aa 11 ee 55
bb 22 cc 33
dd 44
变成这样子了,在两个结果集语句中我有使用ORDER BY