如何在ROW_NUMBER()函数和联合中获取过滤器记录

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

Answer 2

这可能

对您有所帮助 -

SELECT * 
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY e.EmpID DESC) AS RowNo
    FROM (
        SELECT /*TOP 20*/
            e.EmpID,
            e.FirstName
        FROM dbo.Emp e
        ORDER BY e.EmpID DESC
        UNION
        SELECT /*TOP 20*/
            e.EMPID,
            e.FirstName
        FROM dbo.Emp e
        WHERE EXISTS(
            SELECT 1 
            FROM dbo.EMPDetail ed
            WHERE e.EmpID = ed.EmpID
                AND ed.IsActive = 'TRUE'
        ) 
        ORDER BY e.EmpID DESC
    ) t
) t
WHERE t.RowNo BETWEEN 1 AND 20