原语句如下
请教怎么把这条语句构造分页 用以下三个参数
参数 icurrentpageindex 当前页,PageSize 每页显示数,typeID 类别ID
select * from A where ContentID in(select PictureID from B where DirID in (select contentid from F_GetChildren('"+typeID+"'))) order by serialNo desc
请教怎么把这条语句构造分页 用以下三个参数
参数 icurrentpageindex 当前页,PageSize 每页显示数,typeID 类别ID
select * from A where ContentID in(select PictureID from B where DirID in (select contentid from F_GetChildren('"+typeID+"'))) order by serialNo desc
drop proc e_PageBreak
drop table ##Tmp
create procedure e_PageBreak
@PageSize int, --每页显示记录数
@CurPage int, --当前页
@PageCount int output, --总页数
@RecordCount int output --总记录数
as
declare @strSQL nvarchar(2000)--查询SQL语句
set @CurPage = @CurPage - 1
-----计算数记录数--------
set @strSQL = 'select ExamPaperID into ##Tmp from e_ExamPaper, k_Course, e_Section where e_ExamPaper.CourseID = k_Course.CourseID and e_ExamPaper.SectionID = e_Section.SectionID and State=1'
exec sp_executesql @strSQL
select @RecordCount = count(*) from ##Tmp-----计算总页数----------
if( @RecordCount % @PageSize = 0 )
set @PageCount = @RecordCount/@PageSize
else
set @PageCount = @RecordCount/@PageSize + 1-----查询要显示的记录----
set @strSQL = 'select top ' + convert(varchar, @PageSize) + ' ExamPaperID, ExamTitle, SectionName, CourseName, AllPoint, PassPoint, TimeLong, CreateType
from e_ExamPaper, k_Course, e_Section
where e_ExamPaper.CourseID = k_Course.CourseID and e_ExamPaper.SectionID = e_Section.SectionID
and ExamPaperID not in (select top ' + convert(varchar, (@PageSize * @CurPage)) + ' ExamPaperID from e_ExamPaper, k_Course, e_Section
where e_ExamPaper.CourseID = k_Course.CourseID and e_ExamPaper.SectionID = e_Section.SectionID
and State=1 order by ExamPaperID) order by ExamPaperID' exec dbo.sp_executesql @strSQL
drop table ##Tmp
go--------调用示例----------
declare @PageCount int
declare @RecordCount int
exec e_PageBreak 5, 1, @PageCount, @RecordCountprint @PageCount
print @RecordCount
{ //如果是有查询条件
sqlstring = "select top "+pagesize
+" * "
+" from Tb_Menus "
+" where [MenuID] not in "
+" (select top "+pagesize * (pageindex-1)+" [MenuID] "
+" from Tb_Menus "
+" where " + ViewState["Quary"].ToString().Trim()
+" order by MenuID asc) "
+ ViewState["Quary"].ToString().Trim()
+" order by MenuID asc";
}
else
{//如果是没有查询条件
sqlstring = "select top "+pagesize
+" * "
+" from Tb_Menus "
+" where [MenuID] not in "
+" (select top "+pagesize * (pageindex-1)+" [MenuID] "
+" from Tb_Menus "
+" order by MenuID asc) "
+" order by MenuID asc";
}
报错内容 :当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
请问怎么解决
and serialNo not in( select * from tblPictureRM_PictureName where ContentID in(select PictureID from tblPictureRM_PictureDirRelation where DirID in (select contentid from F_GetChildren('"+typeID+"'))) )
order by serialNo desc
我是这样写的 能编译不能执行
and serialNo not in( select 20 serialNo from tblPictureRM_PictureName where ContentID in(select PictureID from tblPictureRM_PictureDirRelation where DirID in (select contentid from F_GetChildren('"+typeID+"'))) )
select top 3 * from jobs where job_id<(select min(job_id) from (select top 6 * from jobs order by job_id desc) as tbl)order by job_id desc3 6 都是参数,自己去改
如
pagesize =3那3那里就是 pagesize
6那里就是 pagesize*2给分吧
select top PageSize* from tb where id>(select max(id) from (select top PageSize*(currPage-1)id from tb order by id)) order by id
2:效率低一些,可靠
select top PageSize* from tb where id not in(select top PageSize*(currPage-1)id from tb order by id) order by id
PageSize:页大小
id:主键
currPage:当前页