22,211
社区成员
![](https://csdnimg.cn/release/cmsfe/public/img/topic.427195d5.png)
![](https://csdnimg.cn/release/cmsfe/public/img/me.40a70ab0.png)
![](https://csdnimg.cn/release/cmsfe/public/img/task.87b52881.png)
![](https://csdnimg.cn/release/cmsfe/public/img/share-circle.3e0b7822.png)
create table ta(UName varchar(10))
go
insert ta select 'aa'
insert ta select 'bb'
insert ta select 'cc'
insert ta select 'dd'
go
create table tb(Score int)
go
insert tb select 1
insert tb select 2
insert tb select 3
insert tb select 4
go
alter table ta add id1 int identity(1,1)
go
alter table tb add id1 int identity(1,1)
go
select a.uname,b.score from ta a,tb b where a.id1=b.id1
go
drop table ta ,tb
/*
uname score
---------- -----------
aa 1
bb 2
cc 3
dd 4
(所影响的行数为 4 行)
*/
--sql 2005
create table A(UName varchar(10))
insert into a values('aa')
insert into a values('bb')
insert into a values('cc')
insert into a values('dd')
create table B(Score int)
insert into b values(1 )
insert into b values(2 )
insert into b values(3 )
insert into b values(4 )
go
select m.uname , n.score from
(select * , px = row_number() over(order by uname) from a) m,
(select * , px = row_number() over(order by score) from b) n
where m.px = n.px
drop table a , b
/*
uname score
---------- -----------
aa 1
bb 2
cc 3
dd 4
(4 行受影响)
*/
alter table ta add id1 int identity(1,1)
go
alter table tb add id1 int identity(1,1)
go
select a.uname,b.score from ta a,tb b where a.id1=b.id1
go
alter table ta drop column id1
go
alter table tb drop column id1
--sql 2000
create table A(UName varchar(10))
insert into a values('aa')
insert into a values('bb')
insert into a values('cc')
insert into a values('dd')
create table B(Score int)
insert into b values(1 )
insert into b values(2 )
insert into b values(3 )
insert into b values(4 )
go
select m.uname , n.score from
(select * , px = (select count(1) from a where UName < t.UName ) + 1 from a t) m,
(select * , px = (select count(1) from b where Score < t.Score ) + 1 from b t) n
where m.px = n.px
drop table a , b
/*
uname score
---------- -----------
aa 1
bb 2
cc 3
dd 4
(所影响的行数为 4 行)
*/
select
a.UName,
b.Score
from
(select px=(select count(1)+1 from A where UName<ta.UName),* from A ta) a,
(select px=(select count(1)+1 from B where Score<tb.Score),* from B tb) b
where a.px=b.px
--sql 2000
select m.uname , n.score from
(select * , px = (select count(1) from a where UName < t.UName ) + 1 from a) m,
(select * , px = (select count(1) from b where Score < t.Score ) + 1 from b) n
where m.px = n.px
--sql 2005
select m.uname , n.score from
(select * , px = row_number() over(order by uname) from a) m,
(select * , px = row_number() over(order by score) from b) n
where m.px = n.px