Intel精英社积分排行

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER PROCEDURE [dbo].[P_GetTopScoreRankByPage]
 @page  int,
 @pageSize int,
 @outCount int OUTPUT
AS

BEGIN

 DECLARE @sql nvarchar(1000)
 DECLARE @ID  bigint

 SET NOCOUNT ON

 -- 获取第一页
 IF @Page = 1
  BEGIN
   SELECT TOP(@PageSize) a.F_Balance,b.F_Gender,a.F_UID,b.F_Nick,b.F_University,b.F_Face FROM IntelScoreDB..T_Score AS a
      INNER JOIN IntelUserDB..T_User as b ON(a.F_UID=b.F_UID) where a.F_Balance>0 ORDER BY a.F_Balance DESC
  END
 ELSE
  BEGIN

           DECLARE @end int

   -- 创建临时表
   CREATE TABLE #PageIndex (IndexID int IDENTITY (1,1),
                 [F_Balance] int,
                 [F_Gender]  int,
                 [F_UID] int,
                 [F_Nick] nvarchar(100),
                 [F_University] nvarchar(100),
                 [F_Face] nvarchar(100)
                 constraint PK_T_TempPagerTable primary key (IndexID))

   -- 计算插入临时表的记录数
   SET @end = @page * @pageSize

   -- 计算临时表中的ID
   SET @ID = (@page - 1) * @pageSize
             INSERT INTO #PageIndex ([F_Balance],[F_Gender],[F_UID],[F_Nick],[F_University],[F_Face])
             SELECT TOP(@end) a.F_Balance,b.F_Gender,a.F_UID,b.F_Nick,b.F_University,b.F_Face  FROM IntelScoreDB..T_Score AS a
     INNER JOIN IntelUserDB..T_User as b ON(a.F_UID=b.F_UID) where  a.F_Balance>0  ORDER BY a.F_Balance DESC           
               
              Select TOP(@pageSize) * FROM  #PageIndex WHERE IndexID> @ID 

        

  END

 
SELECT @OutCount = COUNT(b.F_UID) FROM IntelScoreDB..T_Score AS a
      INNER JOIN IntelUserDB..T_User as b ON(a.F_UID=b.F_UID) where a.F_Balance>0

END

转载于:https://www.cnblogs.com/mygood/articles/1419815.html