在对数据库取值时,我每次取的都是当前页说显示的记录数,存储过程如下:
CREATE PROCEDURE sp_Examinee_Get
(
@pageSize INT =NULL, --每一页显示多少
@pageCode INT =NULL --显示第几页
)
AS
DECLARE @StrSql VARCHAR(1000);
DECLARE @tmp INT; SET @tmp= @pageSize*@pageCode; IF (@pageCode=0)
SET @StrSql ='SELECT top '+ CAST( @pageSize AS VARCHAR(20) ) +' * FROM TblInfo_Examinee where 1=1';
ELSE
SET @StrSql ='SELECT top '+ CAST( @pageSize AS VARCHAR(20) ) +' * FROM TblInfo_Examinee WHERE (PK_ExamineeId> (SELECT MAX(PK_ExamineeId) FROM (SELECT TOP '+ CAST( @tmp AS VARCHAR(20) )+' PK_ExamineeId FROM TblInfo_Examinee ORDER BY PK_ExamineeId ) AS T)) ';
这样就有一个问题,取出来的记录集都是按主键,即PK_ExamineeId 排序的,但如果客户要求按其他的字段排许该怎么办呢,例如按省份来排?苦恼中...我知道可以取出后有defaultviwe可以重新设置排序字段,由于每次取的都是本页的记录,这中重新的排序也只是对本页记录的重排,不是对所有的记录重排。请高手指点。
CREATE PROCEDURE sp_Examinee_Get
(
@pageSize INT =NULL, --每一页显示多少
@pageCode INT =NULL --显示第几页
)
AS
DECLARE @StrSql VARCHAR(1000);
DECLARE @tmp INT; SET @tmp= @pageSize*@pageCode; IF (@pageCode=0)
SET @StrSql ='SELECT top '+ CAST( @pageSize AS VARCHAR(20) ) +' * FROM TblInfo_Examinee where 1=1';
ELSE
SET @StrSql ='SELECT top '+ CAST( @pageSize AS VARCHAR(20) ) +' * FROM TblInfo_Examinee WHERE (PK_ExamineeId> (SELECT MAX(PK_ExamineeId) FROM (SELECT TOP '+ CAST( @tmp AS VARCHAR(20) )+' PK_ExamineeId FROM TblInfo_Examinee ORDER BY PK_ExamineeId ) AS T)) ';
这样就有一个问题,取出来的记录集都是按主键,即PK_ExamineeId 排序的,但如果客户要求按其他的字段排许该怎么办呢,例如按省份来排?苦恼中...我知道可以取出后有defaultviwe可以重新设置排序字段,由于每次取的都是本页的记录,这中重新的排序也只是对本页记录的重排,不是对所有的记录重排。请高手指点。
解决方案 »
- 网站不到一天就cpu占用过高,IIS如何设置
- VSS做管理工具,表示层缺少BLL层引用
- 怎样去掉下载弹出框...
- 如何将 XML 文件绑定到 TREEVIEW 中,??
- 在winForm窗体中,可以用Shockwave Flash Object来显示flash动画,在webForm窗体中,用什么控件来显示flash动画呢?
- 如何打印网页,,打印的网页要包含菜单,工具栏,地址栏
- datagrid中FooterTemplate里控件的定位
- rebuild和build
- 利用浏览器值获文章列表
- 选中dropdownlist后,怎么把值分别显示在两个textbox中
- 滚动datagrid时怎么使对应的信息也跟着滚动
- 请问这个打开新窗口正确的代码该如何写?
ALTER PROCEDURE dbo.GetPagingRecord
(
@tablename varchar(100),--表名或视图表
@fieldlist varchar(4000)='*',--欲选择字段列表
@orderfield varchar(100),--排序字段
@keyfield varchar(100),--主键
@pageindex int,--页号,从0开始
@pagesize int=20,--页尺寸
@strwhere varchar(4000),--条件
@ordertype bit=1--排序,1,降序,0,升序 )
AS
/**//*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
SET NOCOUNT ON declare @sqlstr varchar(6000)
--处理SQL中危险字符,并且将条件处理成易嵌入的形式
set @strwhere=replace(@strwhere,'''','''''')
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
set @sqlstr='declare @CurPageNum int;'
set @sqlstr=@sqlstr+'declare @nextpagenum int;'
set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex+1 as varchar)+'*'+cast(@Pagesize as varchar)+';'
set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+' where '+@strwhere+' order by '+@orderfield+' desc ) as a where '+@keyfield+' not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' desc) order by '+@orderfield+' desc'';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from
'+@tablename+' where '+@strwhere+' order by '+@orderfield+' asc ) as a where '+@keyfield+' not in (
select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where '+@strwhere+'
order by '+@orderfield+' asc) order by '+@orderfield+' asc'';'
end
set @sqlstr=@sqlstr+'execute( @sqlstr)'
print @sqlstr
execute(@sqlstr)
保证好用.///*************************///
这两年来慢慢习惯了光说不练
///*************************///
@class int, --问题的类别
@pageSize int, --每页显示信息数
@currentPage int, --当前页数
@totalPage int output --页总数
as
declare @strQuery as varchar(1000) --查询语句
declare @rowCount as int --总行数
--根据条件查询总记录条数用于分页功能的信息显示
select @rowCount=count(ID) from (select ID from Question where Type=@class and Put_State=1 and Del=0) as temphelp
set @totalPage = ceiling(cast(@rowCount as float)/cast(@pageSize as float)) --根据总条数来获取记录的总分页数
if @currentPage >1 --判断如果当前页大于1就执行下一步
begin
--如果当前页大于总页数,那么就把当前页设为总页数
if @currentPage>@totalPage
begin
set @currentPage = @totalPage
end
--执行SQL查询取出要查询的记录
set @strQuery='SELECT TOP '+cast(@pageSize as varchar(10))+' ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Answer_Date,Handler FROM (select ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Handler from Question where Type='+cast(@class as varchar(10))+' and Put_State=1 and Del=0) as temphelp WHERE id NOT IN (SELECT TOP '+cast((@currentPage-1)*@pageSize as varchar(10))+' id FROM (select ID,Add_Date,Answer_Date from Question where Type='+cast(@class as varchar(10))+' and Put_State=1 and Del=0) as temphelp ORDER BY (Case ord When 1 Then 0 Else 1 End),Add_Date desc) ORDER BY (Case ord When 1 Then 0 Else 1 End),Add_Date desc'
end
else --如果当前页不大于1就直接执行查询略过判断获得记录
begin
set @strQuery ='select top '+cast(@pageSize as varchar(10))+' ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Answer_Date,Handler from (select ID,Title,Contactman,Phone,Email,Type,Content,Answer_Note,Ord,Put_State,Add_Date,Answer_Date,Handler from Question where Type='+cast(@class as varchar(10))+' and Put_State=1 and Del=0) as temphelp ORDER BY (Case ord When 1 Then 0 Else 1 End),Add_Date desc'
end
exec (@strQuery) --执行语句
return
GO