SQL临时表加分页操作

浏览数：22 / 时间：2015年06月12日

常用的分页存储

    DECLARE @SQL NVARCHAR(2000)
    SET @SQL = ‘SELECT @TotalCount=COUNT(1) FROM #temptable‘ + @Condition
    EXEC sp_executesql @SQL, N‘@TotalCount INT OUTPUT‘, @TotalCount OUTPUT 
    DECLARE @ExeSQL NVARCHAR(4000)  
    SET @ExeSQL = ‘   WITH temp
              AS ( SELECT TOP ‘ + @LastRow
        + ‘
                            #temptable.* ,
                            ROW_NUMBER() OVER ( ORDER BY #temptable.MeetID ) AS PX
                   FROM     #temptable ‘ + @Condition + ‘
                   ORDER BY #temptable.MeetID
                 )
        SELECT  *
        FROM    temp
        WHERE   PX >=‘ + @FirstRow 

    EXEC (@ExeSQL)

创建临时表

IF OBJECT_ID(‘tempdb..#tempMettings‘) IS NOT NULL 
        DROP TABLE    #tempMettings

    SELECT  a.Id ,
            a.Metting_DateBet ,
            a.Meeting_DateEnd ,
            a.Metting_Date ,
            a.Metting_Time ,
            a.Meeting_FTime ,
            a.Site ,
            a.SiteType
    INTO    #tempMettings
    FROM    ( SELECT    Id ,
                        Metting_DateBet ,
                        Meeting_DateEnd ,
                        Metting_Date ,
                        Meeting_FDate Metting_Time ,
                        Meeting_FTime ,
                        Site ,
                        SiteType
              FROM      Mobile_Manager_Mettings
              WHERE     SiteType = ‘2‘
                        AND Status NOT IN ( 0, 7, 2 )
            ) a