sql-server - 如何在 ROW_NUMBER() 函数和 Union 中获取 Filter 记录？

Question

我有一个从表中获取结果的联合查询。当只有第一个查询执行时这个工作正常但如果在第二个(联合部分)也返回结果那个时候它将不起作用。我的查询像

Select * from (
Select  ROW_NUMBER() OVER(ORDER BY EmpID DESC) as RowNo,
    Emp.EMPID, EMP.FirstName
    From Emp
Union
Select ROW_NUMBER() OVER(ORDER BY EMPID DESC) as RowNo,
    Emp.EMPID, EMP.FirstName
    From Emp Inner Join EMPDetail On Emp.EmpID = EMPDetail.EMPID 
    Where EMPDetail.IsActive=True
    ) as _EmpTable where RowNo between 1 and 20

请帮帮我。我想使用行号添加分页。还有其他解决方案吗？

Answer 1

在执行 row_number()ing 之前，您需要围绕 union 进行另一个子查询:

Select * 
from 
(
    Select  ROW_NUMBER() OVER(ORDER BY EmpID DESC) as RowNo,
        EmpID, 
        FirstName
    FROM
    (
        SELECT 
            Emp.EMPID, 
            Emp.FirstName
        From Emp

        Union

        Select 
            Emp.EMPID, 
            EMP.FirstName
        From 
            Emp Inner Join EMPDetail On Emp.EmpID = EMPDetail.EMPID 
        Where 
            EMPDetail.IsActive=True
    )
) as _EmpTable 
where 
    RowNo between 1 and 20