--sql 2000 select m.col , n.col from (select t.* , px = (select count(1) from a where col < t.col) + 1 from a t) m full join (select t.* , px = (select count(1) from b where col < t.col) + 1 from b t) n on m.px = n.px--sql 2005 select m.col , n.col from (select t.* , px = row_number() over(order by col) from a t) m full join (select t.* , px = row_number() over(order by col) from b t) n on m.px = n.px
declare @TableA table (col varchar(1)) insert into @TableA select '1' union all select 'y' union all select '3'declare @TableB table (col varchar(1)) insert into @TableB select 'a' union all select 'b' union all select 'c'create table #a (id int identity(1,1),acol varchar(2)) insert into #a(acol) select * from @TableAcreate table #b (id int identity(1,1),bcol varchar(2)) insert into #b(bcol) select * from @TableBselect a.acol,b.bcol from #a a left join #b b on a.id=b.id drop table #a drop table #b/* acol bcol ---- ---- 1 a y b 3 c */
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB ( COL0 VARCHAR(5) ) IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA GO CREATE TABLE TA ( COL0 VARCHAR(5) )INSERT INTO TA SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' INSERT INTO TB SELECT 'Z' UNION ALL SELECT 'Y' UNION ALL SELECT 'X' ;WITH C1 AS ( SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())AS RN,COL0 FROM TA ) ,C2 AS ( SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())AS RN,COL0 FROM TB ) SELECT C1.COL0,C2.COL0 FROM C1 JOIN C2 ON C1.RN=C2.RN------------ COL0 COL0 A Z B Y C X
a列 是1 y 3 ,是 没有排序好,不能比较!这样写变成 1 3 y sql 2000,还是使用临时表,有个自增列,如2L所示
select m.col , n.col from
(select t.* , px = (select count(1) from a where col < t.col) + 1 from a t) m
full join
(select t.* , px = (select count(1) from b where col < t.col) + 1 from b t) n
on m.px = n.px--sql 2005
select m.col , n.col from
(select t.* , px = row_number() over(order by col) from a t) m
full join
(select t.* , px = row_number() over(order by col) from b t) n
on m.px = n.px
declare @TableA table (col varchar(1))
insert into @TableA
select '1' union all
select 'y' union all
select '3'declare @TableB table (col varchar(1))
insert into @TableB
select 'a' union all
select 'b' union all
select 'c'create table #a (id int identity(1,1),acol varchar(2))
insert into #a(acol)
select * from @TableAcreate table #b (id int identity(1,1),bcol varchar(2))
insert into #b(bcol)
select * from @TableBselect a.acol,b.bcol from #a a left join #b b on a.id=b.id
drop table #a
drop table #b/*
acol bcol
---- ----
1 a
y b
3 c
*/
GO
CREATE TABLE TB
(
COL0 VARCHAR(5)
)
IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA
GO
CREATE TABLE TA
(
COL0 VARCHAR(5)
)INSERT INTO TA
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' INSERT INTO TB
SELECT 'Z' UNION ALL
SELECT 'Y' UNION ALL
SELECT 'X' ;WITH C1 AS
(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())AS RN,COL0 FROM TA
)
,C2 AS
(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())AS RN,COL0 FROM TB
)
SELECT C1.COL0,C2.COL0 FROM C1 JOIN C2 ON C1.RN=C2.RN------------
COL0 COL0
A Z
B Y
C X
sql 2000,还是使用临时表,有个自增列,如2L所示