一个查询问题

[Mirror然]

原表为tabel1
id name money
1 A 20
2 B 40
3 C 30
想根据查询得到如下显示
排名 id name money
1 1 A 20
2 3 C 30
3 2 B 40
后面还有更多数据,怎样写好点？（money是降序） 学习

[GEPIN]

怎么没明白你的意思呢？

[bill024]

create table tabel1(id int,name varchar(10),money int)
insert tabel1 select 1,'A',20
union all select 2,'B',40
union all select 3,'C',30

select 排名=(select count(1)+1 from tabel1 where money<a.money),id,name,money from tabel1 a
order by 排名,money desc

drop table tabel1

[bill024]

排名 id name money
----------- ----------- ---------- -----------
1 1 A 20
2 3 C 30
3 2 B 40

[bill024]

改下
select 排名=(select count(1)+1 from tabel1 where money<a.money),id,name,money from tabel1 a
order by 排名

[昵称被占用了]

select 排名=(select count(*) from tabel1 where money<=a.money),id,name,money from tabel1 a
order by a.money

[昵称被占用了]

？（money是降序）

好像列出的实际是升序

[mugua604]

select identity(int,1,1) as 排序,* INTO temp from 表 order by money

select * from temp

[RunUpwind]

--不如用个临时表

select 排名=identity(int),* into #tmp from tabel1 order by money desc

select * from #tmp

drop table #tmp

[mugua604]

个人感觉temptable速度比较快,但要牺牲空间!

[RunUpwind]

select 排名=identity(int),* into #tmp from tabel1 order by money

select * from #tmp

drop table #tmp

[Zack999]

如果money有重复值？

[Mirror然]

谢谢了^_^！ 结贴了!