(3) dnt_getfavoriteslistbyalbum  获取用户收藏列表并分页

CREATE PROCEDURE [dnt_getfavoriteslistbyalbum]

    @uid INT ,

    @pagesize INT ,

    @pageindex INT

AS

    DECLARE @strSQL VARCHAR(5000)

 

    --下边这个语句找出所有用户收藏，临时记录在@strSQL中

    SET @strSQL = 'SELECT [f].[tid], [f].[uid], [albumid], [albumcateid], [userid] AS [posterid], [username] AS [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [createdatetime] AS [postdatetime] FROM [dnt_favorites] [f],[dnt_albums] [albums] WHERE [f].[tid]=[albums].[albumid] AND [f].[typeid]=1 AND [f].[uid]='

        + STR(@uid)

 

    IF @pageindex = 1

        BEGIN   --第一页的处理，简单的SELECT TOP

            SET @strSQL = 'SELECT TOP ' + STR(@pagesize)

                + '  [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime]  FROM ('

                + @strSQL + ') f' + '  ORDER BY [tid] DESC'

        END

    ELSE

        BEGIN   --其他页的处理，跟第1种的处理相似  也是选出( @pageindex - 1 ) * @pagesize个数据，逆序排列后返回前@pagesize个数据

            SET @strSQL = 'SELECT TOP ' + STR(@pagesize)

                + '  [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime]  FROM ('

                + @strSQL

                + ') f1 WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP '

                + STR(( @pageindex - 1 ) * @pagesize) + ' [tid] FROM ('

                + @strSQL + ') f2' + '  ORDER BY [tid] DESC) AS tblTmp)'

                + '  ORDER BY [tid] DESC'

        END

 

    EXEC(@strSQL)  --组织完SQL语句，执行以下就OK了

GO        BEGIN

            SET @strSQL = 'SELECT TOP ' + STR(@pagesize)

                + '  [tid], [uid], [albumid], [albumcateid], [posterid], [poster], [title], [description], [logo], [password], [imgcount], [views], [type], [postdatetime]  FROM ('

                + @strSQL

                + ') f1 WHERE [tid] < (SELECT MIN([tid]) FROM (SELECT TOP '

                + STR(( @pageindex - 1 ) * @pagesize) + ' [tid] FROM ('

                + @strSQL + ') f2' + '  ORDER BY [tid] DESC) AS tblTmp)'

                + '  ORDER BY [tid] DESC'

        END

 

    EXEC(@strSQL)

GO

 

 

(4) dnt_getattentiontopics    分页获取需要被关注的主题列表

CREATE PROCEDURE [dnt_getattentiontopics]   --分页获取需要被关注的主题列表

    @fid VARCHAR(255) = '' ,

    @tpp INT ,     --每页显示数量

    @pageid INT ,   --页码

    @condition NVARCHAR(255) = ''   --搜索条件

AS

    DECLARE @pagetop INT ,

        @strSQL VARCHAR(5000)

 

    SET @pagetop = ( @pageid - 1 ) * @tpp

    IF @pageid = 1

        BEGIN

            SET @strSQL = 'SELECT TOP  ' + STR(@tpp)

                + '  * FROM [dnt_topics] WHERE [displayorder]>=0  AND [attention]=1'

            IF @fid <> '0'

                SELECT  @strSQL = @strSQL + '  AND [fid] IN (' + @fid + ')'

            IF @condition <> ''

                SELECT  @strSQL = @strSQL + @condition

            SELECT  @strSQL = @strSQL + '  ORDER BY [lastpost] DESC'

        END

    ELSE

        BEGIN

            SET @strSQL = 'SELECT TOP  ' + STR(@tpp)

                + '  * FROM [dnt_topics]  WHERE [tid] < (SELECT MIN([tid])  FROM (SELECT TOP '  --也是选择出来顶上的( 页码 - 1 ) * 每页数量个行

                + STR(@pagetop)

                + ' [tid] FROM [dnt_topics]   WHERE [displayorder]>=0  AND [attention]=1'

            IF @fid <> '0'   --加入搜索条件

                SELECT  @strSQL = @strSQL + '  AND [fid] IN (' + @fid + ')'

            IF @condition <> ''

                SELECT  @strSQL = @strSQL + @condition

            SELECT  @strSQL = @strSQL + '   ORDER BY [tid] DESC'

            SELECT  @strSQL = @strSQL + '  )  AS T) '

            IF @fid <> '0'

                SELECT  @strSQL = @strSQL + '  AND [fid] IN (' + @fid + ')'

            IF @condition <> ''

                SELECT  @strSQL = @strSQL + @condition

            SELECT  @strSQL = @strSQL

                    + '  AND [displayorder]>=0  AND [attention]=1 ORDER BY [tid] DESC'

        END

    EXEC(@strSQL)

GO