SQL Server：ROW_NUMBER()查询的CROSS APPLY等效语法

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

编辑：我不想在SELECT语句中使用CTE。

- user2966445

---

有这种方法，但你不会想用。它的效率要低得多。 - Gordon Linoff

除了CROSS APPLY之外，还有其他选项吗？有没有可能更有效率的方法？ - user2966445

那要看情况。如果有两个或更多员工的任职时间相同，你想做什么？ - Joel Coehoorn

@JoelCoehorn 请假设不会出现平局。我只是使用这个简单的例子来找出其他查询选项。在我的实际场景中，不会有平局。 - user2966445

Answer 1

"

交叉应用的等效语句为：

"

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 进行比较以确保唯一编号。

- Gordon Linoff

---

我认为你可以用“TOP 1”样式的应用程序做得更好……虽然仍然不太可能打败row_number()，但有时候也是可以的。 - Joel Coehoorn

@Zack...不需要澄清。row_number()在其行为方面非常明确。 - Gordon Linoff

是的，因为它明确地提供了“不能保证使用ROW_NUMBER()查询返回的行将在每次执行时[具有非唯一列]完全相同排序”的保证。混淆在于OP如何处理潜在的并列情况。由于OP表示并列不是问题，这个问题无论如何都是无意义的。 :) - Zack

@Zack，你可能忽略了“非唯一”的部分。如果OVER子句的ORDER BY部分足够清晰地定义了严格的顺序，那么该函数就是确定性的。 - Joel Coehoorn

Answer 2

您可能有比扫描整个tblEmployee表更好的部门ID来源：

SELECT a.* 
FROM (SELECT DISTINCT DeptID FROM tblEmployee) d
CROSS APPLY (
    SELECT TOP 1 EmpName, DeptID, Tenure
    FROM tblEmployee e
    WHERE e.DeptID = d.DeptID
    ORDER BY Tenure DESC
) a

这个例子不太适合使用APPLY方法，因为你还需要创造源值。当你已经有源数据时（比如你需要部门名称，因此自然从一个想象的tblDepartment表开始），使用APPLY可能更有意义。如果你需要从多个地方获取这种信息（在查询中使用多个APPLY比ROW_NUMBER()更容易），或者APPLY操作本身是一个更复杂的子查询，需要查找多个表时，也可以使用APPLY。

- Joel Coehoorn

Answer 3

实际上，您可以在不使用CROSS APPLY或ROWNUM的情况下完成此操作。您可以创建一个派生表，列出每个部门的最大任期，然后将其与基本员工表连接。使用CTE，您的查询将如下所示：

WITH 
    DeptWithMaxTenure AS
    (
        SELECT DeptID, Max(Tenure) AS MaxTenure
        FROM tblEmployee
        GROUP BY DeptID
    )
SELECT
    tblEmployee.EmpName,
    tblEmployee.DeptID,
    tblEmployee.Tenure
FROM tblEmployee
    INNER JOIN DeptWithMaxTenure ON
        DeptWithMaxTenure.DeptID = tblEmployee.DeptID
        AND DeptWithMaxTenure.MaxTenure = tblEmployee.Tenure

- Zack

---

在两个员工的任期相同的情况下，这可能会为一个部门产生两条记录。 - Joel Coehoorn

谢谢，但我忘了提到我正在尝试避免使用CTE。在复制环境中使用CTE的经验非常糟糕。 - user2966445

@JoelCoehoorn：既然 OP 想要“找到每个部门中任职时间最长的员工”，我认为在这种情况下你会希望返回多个员工（当然，OP 需要澄清）。如果您不想使用 CTE，您可以将表达式直接放入查询中。但是，这实际上与使用 CTE 是一样的。 - Zack

row_number的解决方案没有提供任何绑定打破者，因此在存在绑定时是不确定的 - 这可能是一个潜在的问题。同样的情况也适用于Top 1，它可能会给出不同的结果。 - Cato

请假设在这个简单的例子中不会出现平局。 - user2966445

Answer 4

我尝试了这个，结果比原来慢了4倍

SELECT A.EmpName, A.DeptID, A.Tenure FROM tblEmployee A 
               CROSS APPLY 
                    (SELECT TOP 1 B.ID FROM tblEmployee B 
                          WHERE B.DeptID = A.DeptID Order By Tenure Desc) CA
WHERE CA.ID = A.ID;

- Cato

---

哪种更高效取决于数据分布和索引。https://www.itprotoday.com/software-development/optimizing-top-n-group-queries - Martin Smith

我在想，使用TOP 1是否比计算所有行号然后选择那些为1的行更高效（尽管性能优化器可能有多聪明）- 即我们不关心计算出第50、51、52等行是哪一行 - 我们只需要最大值。 - Cato