看你按什么排序了,假设你是按id排序,你可以这样
select top 11 *
from (
select top 20 *
from yourtable
where id=a.id
order by id desc
)
order by id
select top 11 *
from (
select top 20 *
from yourtable
where id=a.id
order by id desc
)
order by id
from (
select top 20 *
from tablenameorder by id asc
)a
order by id desc
select top 11 * from
(
select top 20 *
from tb
order by [id]
)a
order by [id] desc
declare @x int
declare @y int
set @x=1
set @y=10
select * from tb
where [id] between (@x-1)*@y and @x*@y
declare @x int
declare @y int
set @x=2
set @y=10
select * from tb
where [id] between (@x-1)*@y and @x*@y
但我的id是不连续的整数
如果我要选中按id倒序排第90到100个数据,该如何做?
select *
into #tb
from yourtable
declare @x int
declare @y intset @x=9
set @y=10
select * from #tb
where [id] between (@x-1)*@y and @x*@ydrop table #tb
select id=identity(int,1,1),*
into #tb
from yourtable
declare @x int
declare @y intset @x=9
set @y=10
select * from #tb
where [id] between (@x-1)*@y and @x*@ydrop table #tb
select * from #t where [id]<=20 and [id]>=10
create proc p_qry
@page int --查询第几页
as
set nocount on
if @page=1 --第一页,直接查询即可
begin
set rowcount 10
select * from 表 order by id desc
end
else
begin
--屏蔽的记录条数
set @page=(@page-1)*10
set rowcount @page
select id into #t from 表 order by id desc --查询出结果
set rowcount 10
select * from 表 a
where not exists(select 1 from #t where id=a.id)
order by id desc
end
go--调用
exec p_qry 2
alter table tb1 add [id] decimal(38,0) identity(1,1)
select * from tb1 where [id]<=20 and [id]>=10
alter table tb1 drop column [id]
select * from #t where F<=20 and F>=10
drop table #t
大家的解答我正在消化理解
(select top 20 * from tb order by col1 desc)
order by col1 ASC
select identity(int,1,1) as id,* into #新表名 from yourtable
--然后用新列id来标识,就可以看到20到30之间的记录
select * from #新表名 where bewtten 20 and 30
--再去掉增加上去的新列名就行了