select m.* , n.* from
(select * , px = (select count(1) from 表1 where 编号1 < t.编号1) + 1 from 表1 ) m
full join
(select * , px = (select count(1) from 表2 where 编号2 < t.编号2) + 1 from 表2 ) n
on m.px = n.px
(select * , px = (select count(1) from 表1 where 编号1 < t.编号1) + 1 from 表1 ) m
full join
(select * , px = (select count(1) from 表2 where 编号2 < t.编号2) + 1 from 表2 ) n
on m.px = n.px
select a.编号1 ,a.名称1 ,b.编号2 ,b.名称2
from a a ,b b where a.编号1 = b.编号2 +2 -- 2 为两组编号的差值
-- sql 2005
;WITH
A AS(
SELECT
rowid = ROW_NUMBER() OVER(ORDER BY 编号1),
*
FROM 表1
),
B AS(
SELECT
rowid = ROW_NUMBER() OVER(ORDER BY 编号2),
*
FROM 表2
)
SELECT
A.编号1, A.名称1,
B.编号2, B.名称2
FROM A, B
WHERE A.rowid = B.rowid
insert into 表1 values(1 , 'A')
insert into 表1 values(2 , 'B')
create table 表2 (编号2 int, 名称2 varchar(10))
insert into 表2 values(3 , 'C')
insert into 表2 values(4 , 'D')
goselect m.编号1 , m.名称1 , n.编号2 , n.名称2 from
(select * , px = (select count(1) from 表1 where 编号1 < t.编号1) + 1 from 表1 t) m
full join
(select * , px = (select count(1) from 表2 where 编号2 < t.编号2) + 1 from 表2 t) n
on m.px = n.pxdrop table 表1 , 表2/*
编号1 名称1 编号2 名称2
----------- ---------- ----------- ----------
1 A 3 C
2 B 4 D(所影响的行数为 2 行)*/
SELECT
*
FROM 表1 A, 表2 B
WHERE (SELECT COUNT(*) FROM 表1 WHERE 编号1 <= A.编号1)
= (SELECT COUNT(*) FROM 表2 WHERE 编号2 <= B.编号2)
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (编号1 int,名称1 varchar(1))
insert into #1
select 1,'A' union all
select 2,'B'
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (编号2 int,名称2 varchar(1))
insert into #2
select 3,'C' union all
select 4,'D';with
T1 as (select id=row_number()over(order by 编号1),* from #1),
T2 as (select id=row_number()over(order by 编号2),* from #2)
select a.编号1,a.名称1,b.编号2,b.名称2 from T1 a join T2 b on a.id=b.id/*
编号1 名称1 编号2 名称2
----------- ----------- ----------- -----------
1 A 3 C
2 B 4 D
*/
/*回复:代码20080514001 总:00000000010 */
/*主题:两个表横向联接 */
/*作者:二等草 */
/******************************************//************例子数据 begin****************/
create table 表1 (编号1 int, 名称1 varchar(10))
insert 表1 select 1, 'A'
insert 表1 select 2, 'B'
create table 表2(编号2 int, 名称2 varchar(10))
insert 表2 select 3, 'C'
insert 表2 select 4, 'D'
GO
/************例子数据 end******************//************代码 begin***************/
select * from 表1 a full join 表2 b
on (select count(*) from 表1 where 编号1<=a.编号1)
= (select count(*) from 表2 where 编号2<=b.编号2)
go/************代码 end*****************//************结果 begin***************
编号1 名称1 编号2 名称2
----------- ---------- ----------- ----------
1 A 3 C
2 B 4 D ************结果 end*****************//************清除*************************/
drop table 表1,表2