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

hechenqingtian 2009-03-24 02:31:31
表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语句,在线等
...全文
91 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
sdhdy 2009-03-24
  • 打赏
  • 举报
回复
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 行)
*/
dawugui 2009-03-24
  • 打赏
  • 举报
回复
[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楼已经改过来了,不好意思.

dawugui 2009-03-24
  • 打赏
  • 举报
回复
--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 行受影响)


*/
sdhdy 2009-03-24
  • 打赏
  • 举报
回复
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 2009-03-24
  • 打赏
  • 举报
回复
--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 行)
*/
百年树人 2009-03-24
  • 打赏
  • 举报
回复
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 2009-03-24
  • 打赏
  • 举报
回复
--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

22,210

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧