王者无敌，非常具有挑战性的SQL语句

[hechenqingtian]

表A 有一个字段UName
UName
aa
bb
cc
dd
没有主键，就一列，也没有任何约束

表B 有一个字段Score
Score
1
2
3
4
没有主键，就一列，也没有任何约束

A,B两张表没有任何关系
现在查询两张表
得到结果如下
UName Score
aa 1
bb 2
cc 3
dd 4

怎样写sql语句，在线等

[dawugui]

--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 
  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

[dawugui]

--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 行）
*/

[sdhdy]

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

[dawugui]

--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 行受影响)


*/

[dawugui]

[Quote=引用 1 楼 dawugui 的回复:]
--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
[/Quote]

sql 2000的语句漏写表别名t,在3楼已经改过来了,不好意思.

[sdhdy]

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 行）
*/