select identity(int,1,1) as List_ID,id,va into #test from Your_table order by va select id,va from #test where list_ID=100
select va from a where id=100 order by va
select top 100 * from table order by va
select top 100 * from table order by va 可以么? 不可以说话呀
select top 1* from tb where id not in (select top 100 id from tb order by va) order by va
select top 1* from tb where id not in (select top 99 id from tb order by va) order by va
测试通过:select top 1 va from (select top 100 va from table order by va) as a order by a.va desc
--示例代码:(我这里只有5条数据,100条也是一样的) declare @tb1 table([id] int,va char(1)) insert into @tb1 select 1,'a'union all select 2,'b'union all select 3,'c'union all select 4,'d'union all select 5,'e'select va=max(t.va)from (select top 100 * from @tb1 order by va)t/* va ---- e(所影响的行数为 1 行) */
select top 100 * from table order by va
select * from tabel a,(select top 1 id,va from(select top 100 id,va from table order by va desc)b order by va)c where a.id=c.id order by a.id desc
select top 1 * from (select top 100 * from 表 order by va) a order by va desc
--建测试表 create table tb(id int,va char(10)) --填充数据 declare @id int set @id=1 while @id<=200 begin insert tb select @id,'name'+ltrim(str(@id)) set @id=@id+1 end --查询第100个 select top 1 * from (select top 100 * from tb order by id) a order by id desc--删除测试表 drop table tb /* id va ----------- ---------- 100 name100 (所影响的行数为 1 行) */
select @va=select top 1 va from (select top 100 * from table)a order by va desc
select ID,va from #TempA where NewID=100
from (select top 100 id,va
from table
order by va )
斑竹要求的是第100条数据
select id,va from #test where list_ID=100
可以么?
不可以说话呀
declare @tb1 table([id] int,va char(1))
insert into @tb1
select 1,'a'union all
select 2,'b'union all
select 3,'c'union all
select 4,'d'union all
select 5,'e'select va=max(t.va)from (select top 100 * from @tb1 order by va)t/*
va
----
e(所影响的行数为 1 行)
*/
create table tb(id int,va char(10))
--填充数据
declare @id int
set @id=1
while @id<=200
begin
insert tb select @id,'name'+ltrim(str(@id))
set @id=@id+1
end
--查询第100个
select top 1 * from (select top 100 * from tb order by id) a order by id desc--删除测试表
drop table tb
/*
id va
----------- ----------
100 name100 (所影响的行数为 1 行)
*/