将相关子查询重写为CROSS APPLY

Question

所以我尝试使用CROSS APPLY，但似乎无法正确使用。

我有一些查询如下：

SELECT COL1, COL2
FROM dbo.tableA AS A
WHERE COL3 = (SELECT MAX(COL4)
FROM dbo.tableA AS B
WHERE A.COL1 = B.COL1) AS SUB

我试试这个：

SELECT COL1, COL2
FROM dbo.tableA AS A
CROSS APPLY (SELECT MAX(COL4) AS MAX_DATE
FROM TABLEA AS B
WHERE A.COL1 = B.COL1) AS SUB 

但是当我使用CROSS APPLY时，我总是返回更多的行。我的错误在哪里？

Answer 1

如果您希望两个查询工作相同，则在第二个查询中缺少一个WHERE：

SELECT COL1, COL2
FROM dbo.tableA AS A
CROSS APPLY (SELECT MAX(COL4) AS MAX_DATE
FROM TABLEA AS B
WHERE A.COL1 = B.COL1) AS SUB 
WHERE A.COL3 = SUB.MAX_DATE;

Answer 2

除了APPLY重写之外，因为这是一个自联接，所以也可以使用MAX窗口聚合来完成：

SELECT COL1, COL2
FROM (
SELECT *,
MAX(COL4) OVER (PARTITION BY COL1) AS mx
FROM dbo.tableA
) AS A
WHERE COL3 = mx