还是昨天的问题:存储过程的分页问题,原来以为ok了,现在才发现问题
按zjcxc (邹建) 的做法,原来以为ok了,现在才发现问题.
我要的是排序后再分页,
而zjcxc (邹建) 的做法是
分页后再排序.这样没有意义啊!
http://expert.csdn.net/Expert/topic/2414/2414205.xml?temp=.6967127
或
CREATE Proc p_show
@QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
问题点数：100、回复次数：19Top
1 楼gyx999（）回复于 2003-11-01 00:30:53 得分 0
我如果再QueryStr 中直接加上order by jobpostdate
则出现语法错误Top
2 楼friendliu（无为）回复于 2003-11-01 00:38:15 得分 10
@FdOrder nvarchar (1000)='' --排序字段列表
@FdOrder这个变量就是你要排序的字段，如果你给这个变量赋值
'select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
就会排序后再分页。
你的存储过程@FdOrder默认值为空，就没有排序。Top
3 楼gyx999（）回复于 2003-11-01 01:10:50 得分 0
没有啊@FdOrder为order by jobpostdate
但却是分页后再排序Top
4 楼txlicenhe（马可）回复于 2003-11-01 08:10:51 得分 10
eg:
use northwind
go
1:Select * from (Select top 10 * from Employees order by FirstName) a
2:Select * from (Select top 10 * from Employees ) a order by Lastname
3:Select * from (Select top 10 * from Employees order by FirstName) a
order by Lastname
都没有问题啊
Top
5 楼zjcxc（邹建）回复于 2003-11-01 08:14:56 得分 10
改用这个完整的过程.
/*--用存储过程实现的分页程序
显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
--邹建 2003.09--*/
/*--调用示例
exec p_show '地区资料'
exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO
CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
Top
6 楼zjcxc（邹建）回复于 2003-11-01 08:18:13 得分 10
因为要顾及通用性,所以对带排序的查询语句有一定要求.如果先排序,再出结果.就是:
exec p_show 'select top 100 percent * from 地区资料 order by 地区名称',5,3,'地区编号,地区名称,助记码'
--查询语句加上:top 100 percentTop
7 楼gyx999（）回复于 2003-11-01 23:10:18 得分 0
在php中调用,其中$jobkey为空
$query="exec sinconic_select '$jobkey','website','$province','$orderby'";
CREATE PROCEDURE sinconic_select
@jobkey varchar(30),
@website varchar(100),
@province varchar(15),
@myorder varchar(30)
AS
if ((len(@province) <> 0)and(len(@jobkey)<>0))
Top
8 楼gyx999（）回复于 2003-11-01 23:26:52 得分 0
上面错发了!
可以了,但感觉速度并不快啊.
我想,只取出想要的一部分数据,速度应该很快才对啊Top
9 楼pengdali（）回复于 2003-11-01 23:40:56 得分 10
不是说了这样最简单方便了吗？
CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GOTop
10 楼pengdali（）回复于 2003-11-01 23:41:12 得分 11
这样做速度绝对快！！！Top
11 楼gyx999（）回复于 2003-11-01 23:47:27 得分 0
pengdali(大力 V3.0)
你一直说这个好用,那怎么用呢,能否举个例子
@lastProductID是什么意思啊Top
12 楼pengdali（）回复于 2003-11-01 23:59:12 得分 0
是你上一比记录取得的最后一个编号，这样效率可以提高很多。Top
13 楼pengdali（）回复于 2003-11-01 23:59:47 得分 0
这样可以不需要每次都重新计算应该从那里开始取了。Top
14 楼mrfinger（ff）回复于 2003-11-02 10:50:28 得分 10
http://www.51base.com/article/search.asp?page=1&action=search&search=%B4%E6%B4%A2%B9%FD%B3%CC+%B7%D6%D2%B3&typeid=1Top
15 楼ok1234567（ok1234567）回复于 2003-11-03 18:34:05 得分 10
SET ROWCOUNT @pageSize
不能获得有序的结果，这可能是一个致命的缺憾^=^Top
16 楼ok1234567（ok1234567）回复于 2003-11-03 18:48:17 得分 0
老实说，分页是一个十分恼人的问题
因为数据是动态的
当数据特别大的时候（我的一个新闻数据库，记录100万，在WEB上发布）
效率是问题
我也正在寻找在MS SQL Server上分页的最优化的技术
我使用ISAPI写接口，使用ODBC API操作数据库
我目前使用一种蹩脚的方法：
就是使用存储过程定位一个分页的首位两条记录（时间datetime,毫秒，新闻就是这样的)
然后以普通的查询获得该页的详细资料
如果一页的记录有限，比如100条以下，完全可以用一个格式化的string（8k）将数据一次性传回去
在接口程序中解析id,title,date等
Top
17 楼zjcxc（邹建）回复于 2003-11-04 08:15:30 得分 19
如果在web是使用,不如用ado自己的分页功能.Top
18 楼zjcxc（邹建）回复于 2003-11-04 09:01:39 得分 0
程序中的话,最好用ADO的分页功能来实现.
'** ----- 数据库连接字符串模板 ---------------------------------------
'** ACCESS数据库
'** iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
'** "Password=""密码"";Data Source=数据库名"
'**
'** SQL数据库
'** iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
'** "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
'ASP中的分页例子,以SQL数据库为例,ACCESS数据库只需要更改连接字符串
<%
iConc = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
Set iRe =Server.CreateObject("ADODB.Recordset")
With iRe
.CursorLocation = adUseClient
.Open "表名", iConc, 1,1
.PageSize = 10 '每页的大小
iCount = .PageCount '总页数
.AbsolutePage = 2 '设置当前显示第几页,这里是第2页
For iJ = 1 To .PageSize '循环显示当前页的记录
'这里改为显示处理的代码
.MoveNext
If .EOF Then Exit For
Next
End With
iRe.Close
Set iRe = Nothing
%>
Top
19 楼gyx999（）回复于 2003-11-04 11:57:16 得分 0
问题没有得到解决.\
我现在干脆使用select top 300 *
woTop
