**在一个有1000条数据的表中，我想取出第500条到第600条数据，如何做？****

[Rainbow686]

题设：
表：test
字段：id content （id为主键）
共有1000条记录，我想取出第500条到第600条数据
用存储过程如何做？

[cheng79802003]

SELECT top 100 *　from table where 主键　not in
(select top 500 主键 from table)

[skyboy0720]

id是否连续？

[stzjzs]

嘿嘿
以后在遇到这样的情况，在列前添加一个标十种子，一切都ok
不管你取第几条到第几条

[loverpyh]

select top 100 * from (select top 600 * from table order by id desc)) order by id

[deodarsydn]

CREATE PROCEDURE usp_1 ( @beid int,--开始id
@endid int, --结束id
@table varchar(100)) --表明
as
declare @sql varchar(8000)
--if @endid>@beid or @table is null return
select @sql ='select top '+ convert(varchar(10),@endid-@beid) +
+' * from '+ @table
+' where id >=(select max(id) '
+' from(select top '+ convert(varchar(10),@beid) + ' * '
+' from ' + @table + ' order by id) t '
+' ) '
+' order by id'
exec (@sql)
go

exec usp_1 500,600,'psm_employee'
go
drop procedure usp_1

[Rainbow686]

学习中

[Rainbow686]

按照以上同志的说法：
假设我的表中有100万条数据，我需要查询出最后的100条，这样的话效率就很低了，有没有什么方法，使查询表中任意一些数据的效率都很高呢？希望高手指点！

[xiyang2170]

Select Top 100 F.*
From F
Where F.ID NOT IN (Select Top 400 A.ID
From A
Order By A.CreateTime ASC) --表F中的ID不等于表A中的前10条
Order BY F.CreateTime ASC

[Rainbow686]

楼上的兄弟这样执行效率会更高一点么？

[xiaoxiangqing]

select top 600 * from test where id not in (select top 500 id from text)

[xiaoxiangqing]

寫錯了
select top 100 * from test where id not in (select top 500 id from text)

[xiaoxiangqing]

select * from
(select top 600 * from text order by id) a
left join
(select top 500 id from text order by id) b
on a.id=b.id where b.id is null

[ouyld]

select * form 表名 where between count(第一列)=500 and count(第一列)b=600

[aohan]

上面的测试过吗，应该有语法错误吧,如果有标识字段的话，可以取出大于500标识字段的TOP 100条记录

[General521]

CREATE proc fy
@rows bigint,
@page bigint
as
declare @sum bigint
if (@rows=0)
begin
raiserror('Please input rows>0 !',16,1)
return
end
if (@page=0)
begin
raiserror('Please input page>0 !',16,1)
return
end
begin
begin transaction
set @sum=@rows*@page
set rowcount @sum
select *
into #t
from 表
order by id asc
select *
into #b
from #t
order by id desc
set rowcount @rows
select *
into #c
from #b
order by id desc
select *
from #c
order by id asc
drop table #t
drop table #b
drop table #c
commit transaction
end
GO


这是个分页的过程,可以得到任何部分的数据.

[Rainbow686]

up

[vzxq]

用存储过程

[xiayule]

SELECT top 100 *　from table where 主键　not in
(select top 500 主键 from table)

[liuspcn]

gz

[ww6166]

up