代码
USE
[
WebE
]
GO
/* ***** 对象: StoredProcedure [dbo].[P_FPosts_ModulePostsOutput] 脚本日期: 07/29/2010 20:43:47 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 某模块的帖子主题
CREATE Procedure [ dbo ] . [ P_FPosts_ModulePostsOutput ]
@ModuleID varchar ( 10 ),
@PageSize int ,
@Page int
As
-- Step 1 创建表变量
-- 所有分页筛选后的POSTID
declare @ZModulePosts_AllInfo_Temp Table
(
PostID varchar ( 10 ),
ModuleID varchar ( 10 ) ,
RowNumber bigint
) ;
-- Step 2 分页输出
-- 定义第一行记录
declare @firstRow int ;
-- 定义最后一条记录
declare @lastRow int ; -- 设置第一条记录
set @firstRow = (( @Page - 1 ) * @PageSize ) + 1 ;
-- 设置最后一条记录
set @lastRow = @firstRow + @PageSize ; -- 定义查询SQL字段
insert into @ZModulePosts_AllInfo_Temp
select * from
( select ForumPostID.PostID,ForumPostID.ModuleID, Row_Number() over ( order by PostID Desc ) as RowNumber from ForumPostID
where ForumPostID.ModuleID = @ModuleID
) as TB
where TB.RowNumber >= @firstRow and TB.RowNumber < @lastRow
-- --------------分页输出完毕--------
-- Step 3 联表输出
select ForumPosts.PostID,ForumPosts.Title,ForumPosts.ModuleID,ForumModules. [ Name ] , ForumPosts.UserName,ForumPosts.PubTime,ForumPosts.LastReplier,ForumPosts.LastReplyTime,ForumPosts.IsDelete,ForumPosts.IsLock
from ForumPosts ,ForumModules , @ZModulePosts_AllInfo_Temp as ZMAT
where
ZMAT.PostID = ForumPosts.PostID
and ForumModules.ModuleID = @ModuleID
and ForumPosts.IsDelete = 0 -- 筛选已删除的
Order by PostID desc
GO
/* ***** 对象: StoredProcedure [dbo].[P_FPosts_ModulePostsOutput] 脚本日期: 07/29/2010 20:43:47 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 某模块的帖子主题
CREATE Procedure [ dbo ] . [ P_FPosts_ModulePostsOutput ]
@ModuleID varchar ( 10 ),
@PageSize int ,
@Page int
As
-- Step 1 创建表变量
-- 所有分页筛选后的POSTID
declare @ZModulePosts_AllInfo_Temp Table
(
PostID varchar ( 10 ),
ModuleID varchar ( 10 ) ,
RowNumber bigint
) ;
-- Step 2 分页输出
-- 定义第一行记录
declare @firstRow int ;
-- 定义最后一条记录
declare @lastRow int ; -- 设置第一条记录
set @firstRow = (( @Page - 1 ) * @PageSize ) + 1 ;
-- 设置最后一条记录
set @lastRow = @firstRow + @PageSize ; -- 定义查询SQL字段
insert into @ZModulePosts_AllInfo_Temp
select * from
( select ForumPostID.PostID,ForumPostID.ModuleID, Row_Number() over ( order by PostID Desc ) as RowNumber from ForumPostID
where ForumPostID.ModuleID = @ModuleID
) as TB
where TB.RowNumber >= @firstRow and TB.RowNumber < @lastRow
-- --------------分页输出完毕--------
-- Step 3 联表输出
select ForumPosts.PostID,ForumPosts.Title,ForumPosts.ModuleID,ForumModules. [ Name ] , ForumPosts.UserName,ForumPosts.PubTime,ForumPosts.LastReplier,ForumPosts.LastReplyTime,ForumPosts.IsDelete,ForumPosts.IsLock
from ForumPosts ,ForumModules , @ZModulePosts_AllInfo_Temp as ZMAT
where
ZMAT.PostID = ForumPosts.PostID
and ForumModules.ModuleID = @ModuleID
and ForumPosts.IsDelete = 0 -- 筛选已删除的
Order by PostID desc
数据库表冗余存储+PostID表+分页