SQL Server 使用 union all 和分页

当我们使用 union all 从两个表获取结果时，如何应用分页。下面是这里的代码，我在已用于分页的“row_num”列中获取重复值。

;WITH resultSetCTE AS 
  (
SELECT
    ROW_NUMBER() OVER 
    (
        ORDER BY nr.is_read,nr.is_read_cashier,
    CASE @columnSortNameDirection WHEN 'Pin' THEN r.name END ASC,
    CASE @columnSortNameDirection WHEN 'PinDesc' THEN r.firstname END DESC  
    ) AS 'row_num',
    r.clave_receiver AS 'Pin',
    r.id_branch AS 'Id_Branch',
    r.id_receiver AS 'Id_receiver',  
    s.name_sender AS 'SenderFullName',
    r.name_receiver AS 'ReceiverFullName',
    r.id_flag_receiver AS  'Status',
    pas.option_name AS 'ApprovalStatus',
    r.mode_pay_receiver AS 'PaymentModeId'


    UNION ALL

    SELECT
    ROW_NUMBER() OVER 
    (
    ORDER BY nr.is_read,nr.is_read_cashier,
    CASE @columnSortNameDirection WHEN 'Pin' THEN r.name END ASC,
    CASE @columnSortNameDirection WHEN 'PinDesc' THEN r.firstname END DESC  
    ) AS 'row_num',
    r.clave_receiver AS 'Pin',
    r.id_branch AS 'Id_Branch',
    r.id_receiver AS 'Id_receiver',  
    s.name_sender AS 'SenderFullName',
    r.name_receiver AS 'ReceiverFullName',
    r.id_flag_receiver AS  'Status',
    pas.option_name AS 'ApprovalStatus',
    r.mode_pay_receiver AS 'PaymentModeId'

 )
    SELECT *,
 (SELECT COUNT(1) FROM resultSetCTE) AS 'RecordCount' 
 FROM resultSetCTE 
 WHERE row_num BETWEEN (1 - 1) * 15 + 1 AND 1 * 15  
 ORDER by IsRead,IsReadCashier

请使用 offset / fetch 代替。它可以解决你所有的问题。 更多信息请点击这里：https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

代码示例（请根据您的需要更新）：

;WITH [resultSetCTE] AS 
 (
    SELECT
         [col] = [object_id] 
    FROM 
        [sys].[tables]

    UNION ALL

    SELECT
         [col] = [object_id] 
    FROM 
        [sys].[tables]
)
SELECT 
     * 
FROM 
    [resultSetCTE] 
ORDER BY 
     [col]
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;