if OBJECT_ID('tb') is not null drop table tb create table tb(id int identity,col1 int,col2 int) insert tb(col1,col2) select 134, 554 union all select 134, 553 union all select 134, 552 union all select 134, 551 union all select 150, 600 union all select 134, 555--2005-2008 select *,排名=row_number() over(order by col2 desc) from tb --2000方法 select *,排名=(select COUNT(1) from tb where col2>=a.col2) from tb a order by 排名 /* id col1 col2 排名 5 150 600 1 6 134 555 2 1 134 554 3 2 134 553 4 3 134 552 5 4 134 551 6 */请看例子
谢谢了 不过 TQuery不支持嵌套查询
不是吧,那用临时表 if object_id('tempdb.dbo.#tb') is not null drop table #tb go select a.* ,id=identity(int,1,1) into #tb from a order by ....select * from #tb
create table tb(id int identity,col1 int,col2 int)
insert tb(col1,col2)
select 134, 554
union all select 134, 553
union all select 134, 552
union all select 134, 551
union all select 150, 600
union all select 134, 555--2005-2008
select *,排名=row_number() over(order by col2 desc) from tb
--2000方法
select *,排名=(select COUNT(1) from tb where col2>=a.col2)
from tb a
order by 排名
/*
id col1 col2 排名
5 150 600 1
6 134 555 2
1 134 554 3
2 134 553 4
3 134 552 5
4 134 551 6
*/请看例子
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
select a.* ,id=identity(int,1,1) into #tb from a order by ....select * from #tb
没有很多吧,最终的语句都是运行在sql服务器上和Tquery又没有太大关系