SQL 服务器 : CROSS APPLY equivalent for ROW_NUMBER() query

Question

我正在尝试寻找一种不同的方法来使用 CROSS APPLY 编写 ROW_NUMBER() 查询，以便比较性能。

在下面的简单示例中，创建了一个员工表，插入了一些测试数据，并使用带有 ROW_NUMBER() 窗口函数的 SELECT 来查找员工在任期最长的每个部门。

是否有另一种使用CROSS APPLY 编写SELECT 的方法？

CREATE TABLE [dbo].[tblEmployee]
(
    [ID] [INT] NOT NULL,
    [DeptID] [TINYINT] NOT NULL,
    [EmpName] [VARCHAR](100) NOT NULL,
    [Tenure] [TINYINT] NOT NULL,

    CONSTRAINT [PK_tblEmployee] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.tblEmployee (ID, DeptID, EmpName, Tenure) 
VALUES ('1', '1', 'John', 2),
       ('2', '1', 'Mary', 5),
       ('3', '2', 'Joe', 3),
       ('4', '3', 'Bill', 10),
       ('5', '2', 'Marilynn', 9),
       ('6', '3', 'Sue', 7);

SELECT 
    EmpName, DeptID, Tenure 
FROM  
    (SELECT 
         EmpName, DeptID, Tenure, 
         ROW_NUMBER() OVER(PARTITION BY DeptID ORDER BY Tenure DESC) TenureRank
     FROM 
         tblEmployee) e 
WHERE 
    e.TenureRank = 1
ORDER BY 
    DeptID

编辑:我宁愿不使用 CTE 作为 SELECT

的一部分

Answer 1

cross apply 等价物是:

select e.*, a.seqnum
from tblEmployee e cross apply
     (select count(*) as seqnum
      from tblEmployee e2
      where e2.deptid = e.deptid and
            (e2.tenure > e.tenure or
             e2.tenure = e.tenure and e2.id >= e.id
            )
     ) a;

你不会想这样做，因为它比 row_number() 效率低得多。请注意使用 id 进行比较以确保唯一编号。