sql 查询排列第几

create table ta(id smallint identity(1,1),name varchar(8),price int) --建表

insert into ta select 'a0',20 union all select 'a1',21 union all select 'a2',12 union all select 'a3',17 union all
select 'a4',22 union all select 'b1',33 union all select 'b3',14 union all select 'c2',46 union all select 'c3',32  --添加data

select * from ta --show data

create procedure t_proc --建存储过程
@flag int
as
if @flag=1
begin    --查询出price最高的一条记录(如46)
select top 1 * from ta order by price desc
end
else if @flag=2
begin    --查询出price第二高的一条记录(如33)
select top 1 * from ta where id not in(select top 1 id from ta order by price desc) order by price desc
end
else if @flag=3
begin    --查询出price排名在第三到第六的记录
select top 4 * from ta where id not in(select top 2 id from ta order by price desc) order by price desc
end
else if @flag=4
begin --查询出price排名在第七到第九的记录
select top 3 * from ta where id not in(select top 6 id from ta order by price desc) order by price desc
end
go

exec t_proc 1 --执行
/*----result (结果)
8c246
*/
exec t_proc 2
/*----result
6b133
*/
exec t_proc 3
/*----result
9c332
5a422
2a121
1a020
*/
exec t_proc 4
/*----result
4a317
7b314
3a212
*/

---

答者:Hopewell_Go(好的在后頭﹗希望更好﹗﹗) 信誉:100 级别:user2 日期:2006-11-18 8:49:03 id:37894036

id  name price
1   a0    20
2   a1    21
3   a2    12
4   a3    17
5   a4    22
6   b1    33
7   b3    14
8   c2    46
9   c3    32

select *,identity(int,1,1) TID into #table from tableName
order by price

select * from #table这样的虚拟表可以实现你的要求