关于SQL中RowNum分页

USE [TIBCOSTG]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Tomas Shao>
-- Create date: <2011/04/22>
-- Description:    <Fetch the result of DWOrders filter by customer>
-- =============================================
ALTER PROCEDURE [dbo].[WS_RC_GetDWOrdersByFilter]
(       
      @queryDate int = NULL,    -- Whether query adddate  1 do  0 not
      @startDate varchar(25) = NULL,    -- Start Date for the Invoice Range Filter
      @endDate varchar(25) = NULL,        -- End Date for the Invoice Range Filter
      @endUserID varchar(33) = NULL,    -- EU ID for the EU Filter
      @endUserName nvarchar(200) = NULL,-- EU Name for the EU Filter
      @orderID varchar(33) = NULL,        -- Order ID for the InvoiceID Filter
      @poNumber varchar(33) = NULL,    -- custPO for the Invoice# Filter
       
        @fldName varchar(255),
        @PageSize   int ,          -- page size
        @PageIndex  int ,         -- page
        @Sort varchar(255),      --asc or desc
        @DoCount int = null
)
AS
declare @tblName varchar(255)
declare @fldNameTemp varchar(255)
declare @strWhere  varchar(1500)
declare @strSQL   varchar(5000)      
declare @strTmp   varchar(110)       
declare @strOrder varchar(400)       
declare @strGetFields varchar(1000)

set @fldNameTemp = @fldName
set @tblName = 'dbo.DW_Order ord
        LEFT JOIN dbo.DW_Organisation org ON ord.customerId = org.commonId
        LEFT JOIN dbo.DW_Person p ON ord.salesRep = p.commonId'
set @strGetFields = 'ord.commonId as commonId, ord.addDate, ord.custPO, ord.orderNumber, org.name AS customerName, p.firstName + '' '' + p.lastName AS salesRep, ord.totalPrice'
    if @queryDate = 1
        BEGIN
            set @strWhere = ' CONVERT(VARCHAR, ord.addDate, 120) BETWEEN ''' + @startDate + ''' AND ''' + @endDate + ''''
        END
    else
        BEGIN
            set @strWhere = '1 = 1 '
        END
       
    if @endUserID != ''
        set @strWhere = @strWhere + ' AND org.commonId = ''' + @endUserID + ''''
    else if @endUserName != ''
        set @strWhere = @strWhere + ' AND org.name = ''' + @endUserName + ''''
    else if @poNumber != ''
        set @strWhere = @strWhere + ' AND ord.custPO = ''' + @poNumber + ''''
    else if @orderID != ''
        set @strWhere = @strWhere + ' AND ord.commonId = ''' + @orderID + ''''

    if @fldName = 'salesRep'
        set @fldNameTemp = 'p.firstName + '' '' + p.lastName'
    else if @fldName = 'customerName'
        set @fldNameTemp = 'org.name'

if @doCount != 0
    begin
    if @strWhere != ''''
    set @strSQL = 'select count(ord.commonId) as Total from ' + @tblName + ' where '+@strWhere
    else
    set @strSQL = 'select count(ord.commonId) as Total from ' + @tblName + ''
    end
else
    begin
    SET   ANSI_NULLS   OFF
    if @PageIndex = 1
        set @strSQL = 'select * from (SELECT ROW_NUMBER() OVER(ORDER BY '+@fldNameTemp+' '+@Sort+') AS RowNum, '+@strGetFields +
        ' from '+@tblName+ ' where ' + @strWhere + ') as t where t.RowNum between 1 and ' + str(@PageSize)
    else
        set @strSQL = 'select * from (SELECT ROW_NUMBER() OVER(ORDER BY '+@fldNameTemp+' '+@Sort+') AS RowNum, '+@strGetFields +
        ' from '+@tblName+ ' where ' + @strWhere + ') as t where t.RowNum between '+str(@PageIndex*@PageSize+1) +' and ' + str((@PageIndex+1)*@PageSize)
end

exec (@strSQL)
--print(@strSQL)
RETURN

 

 

注：ORDER BY中的数据库字段不用使用别名的字段，否则排序无效。