--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
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
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
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 )
goselect 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.pxdrop table a , b /*uname score
---------- -----------
aa 1
bb 2
cc 3
dd 4(所影响的行数为 4 行)
*/
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
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 )
goselect 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.pxdrop table a , b /*
uname score
---------- -----------
aa 1
bb 2
cc 3
dd 4(4 行受影响)
*/
sql 2000的语句漏写表别名t,在3楼已经改过来了,不好意思.
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 行)
*/