SQL Server使用联合所有和分页

Question

当我们使用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

Answer 1

尽管您还没有发布完整的查询，但是您可以尝试这样的操作

declare @columnSortNameDirection varchar(5)
;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',*  from (    SELECT    
    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

    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') as A

 )
  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