查询第X页,每页Y条记录最基本的处理方法:如果表中有主键(记录不重复的字段也可以),可以用类似下面的方法,当然x,y要换成具体的数字,不能用变量:select top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)如果表中无主键,可以用临时表,加标识字段解决.这里的x,y可以用变量.select id=identity(int,1,1),* into #tb from 表
select * from #tb where id between (x-1)*y and x*y-1
select * from #tb where id between (x-1)*y and x*y-1
一个分页显示数据的例子
CREATE PROCEDURE sp_SearchResults
@SessionID int,
@PageSize int,
@CurrentPage int,
@RecordCount int Output,
@PageCount int Output
ASdeclare @BeginRow int
declare @EndRow int
declare @BeginID int
declare @EndID int--取出某SessionID的当前查询结果,使用临时表#temp的作用主要是缩小查询范围
select * into #temp from 人事_员工基本信息表 where 员工ID = @SessionIDset @RecordCount = (select count(*) from #temp)set @PageCount = Round((@RecordCount / @PageSize), 0) + 1--如果@PageSize刚刚能整除@RecordCount,则@PageCount减1
if @RecordCount = ((@PageCount - 1) * @PageSize)
set @PageCount = @PageCount - 1--如果当前页的数值大于总页数,则把当前页重设为总页数的值
if (@CurrentPage > @PageCount)
set @CurrentPage = @PageCount--当结果集不为空时,查询返回当前页的结果集
if @RecordCount > 0
begin
--取的当前页的开始位置和结束位置
set nocount on
set @BeginRow = @PageSize * (@CurrentPage - 1) + 1
set rowcount @BeginRow
select @BeginID = 员工id from #temp order by 员工id
set @EndRow = @PageSize * @CurrentPage
if @EndRow >= @RecordCount
set @EndRow = @RecordCount
set rowcount @EndRow
select @EndID = 员工id from #temp order by 员工id
set rowcount 0
set nocount off
--取位置结束 --根据开始位置和结束位置返回当前页的记录
select *
from #temp
where 员工id between @BeginID and @EndID
order by 员工id
end
else
select * from #tempdrop table #temp
GO
Create Proc sp_getPage
(@strSQL nvarchar(4000),
@pageCount int=1,
@pageSize int=999999)
As
begin
set nocount on
declare @p1 int
set @pageCount=(@pageCount-1)*@pageSize+1
exec sp_cursorOpen @p1 output, @strSQL
exec sp_cursorFetch @p1,16,@pageCount,@pageSize
exec sp_cursorClose @p1
end ----Invoke:
sp_getpage 'select * from sysobjects',1,20
string sql="select top 20 * from BBs_post where PostID not in(select top (5-1)*20 PostID from bbs_post)";
SqlConnection cn=new SqlConnection("data source=(local);initial catalog=test;user id=sa;pwd=beijisoft");
SqlCommand cm=new SqlCommand(sql,cn);
SqlDataReader DataReader1;
cn.Open();
DataReader1 = cm.ExecuteReader();
string i="";
if(DataReader1.Read())
{
i=i+DataReader1.GetInt32(0).ToString();
}
else
{
Response.Write("111");
}
Response.Write(i);老是报错说
异常详细信息: System.Data.SqlClient.SqlException: 第 1 行: '(' 附近有语法错误。
但是我换段sql语句就没有错了
这个语句有没有错
PostID是主键