高手進！問一分頁加篩選加排序的sql！
現在有這樣一個SQL：
select top 50 * from
(select * from ( select * from v_cmm_club_prize_joined_users where prizecode=3 )g
where ID not in (select top 0 id from
( select * from v_cmm_club_prize_joined_users where prizecode=3 )g ))a
用於分頁加查詢，其中v_cmm_club_prize_joined_users 寫法：
SELECT TOP 100 PERCENT b.ORG_NAME AS orgname, a.id, a.psid, a.prizecode, a.joindate, a.username, a.tel, a.gatewayid, a.telprize, a.pwd, a.orgid
FROM dbo.CMM_CLUB_PRIZE_JOINED_USERS AS a INNER JOIN
dbo.SYS_ORGANIZATION AS b ON a.orgid = b.ORG_ID
ORDER BY a.prizecode DESC, a.id
程序中這樣來得到一頁的數據：
rs=stmt.executeQuery( "select count(*) as amount from v_cmm_club_prize_joined_users "+ where);
if (rs.next())
dataSetSize=rs.getInt( "amount ");
String view= "(select * from v_cMM_club_prize_joined_users "+where + ") g ";
String sql= "select top 50 * from (select * from "+view+ " where ID not in (select top "+String.valueOf(startRow)+ " id from "+view+ " ))a ";
rs=stmt.executeQuery(sql);
想法是通過where 查詢后還可以先將查詢以後的結果集按照prizecode,id排序后再分業。
但是在sqlserver2000裏可以達到我的要求，到了２００５數據的顯示卻好像是無序的，這是怎麽囘事呢
[解决办法]
select top 50 * from
(select * from ( select * from v_cmm_club_prize_joined_users where prizecode=3 )g order by ...
在外层再排一次序
[解决办法]
用邹老大这个：
效率还不错
/*--用存储过程实现的分页程序
显示指定表、视图、查询结果的第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
