了解為什麼在使用子查詢過濾器時讓每個父行與其最新的子行加入不起作用

我有一個父子表關係，我試圖讓每個父行及其最新的子行詳細資訊。

我嘗試使用子查詢來做到這一點沒有奏效，我只能使用 CTE 來做到這一點。

我想了解：

1. 為什麼子查詢方式不起作用？
2. 有沒有辦法解決它？

這是父子關係的玩具範例（完整展示在這裡）：

CREATE TABLE Cities
   (
       [Id] INT PRIMARY KEY,
       [Name] NVARCHAR(100),
       [EstablishedDate] DATETIME2 NULL,
   );
   
CREATE TABLE Parks
   (
       [Id] INT PRIMARY KEY,
       [Name] NVARCHAR(100),
       [OpeningDate] DATETIME2 NULL,
       [CityId] INT FOREIGN KEY REFERENCES Cities(Id)
   );

使用 CTE 成功查詢：

;WITH NumberedParks AS
(
 SELECT *, ROW_NUMBER() OVER (PARTITiON BY CityId ORDER BY OpeningDate DESC) AS rownum
 FROM Parks
)
SELECT * 
FROM Cities
JOIN NumberedParks on Cities.Id = NumberedParks.CityId
WHERE rownum = 1;

失敗的嘗試：

SELECT * 
FROM Cities
JOIN Parks on Cities.Id = Parks.CityId
WHERE EXISTS
(
 SELECT TOP 1 Id
 FROM Parks inner_parks
 WHERE inner_parks.Id = Parks.Id
 ORDER BY inner_parks.OpeningDate DESC
)

SELECT * 
FROM Cities
JOIN Parks on Cities.Id = Parks.CityId
WHERE Parks.Id =
(
 SELECT TOP 1 Id
 FROM Parks inner_parks
 WHERE inner_parks.Id = Parks.Id
 ORDER BY inner_parks.OpeningDate DESC
);

SELECT * 
FROM Cities
JOIN Parks on Cities.Id = Parks.CityId
WHERE Parks.Id =
(
 SELECT TOP 1 Id
 FROM Parks inner_parks
 WHERE inner_parks.Id = Parks.Id
 ORDER BY inner_parks.OpeningDate DESC
);

在此查詢中，子查詢從 Parks 表中獲取最近的（基於開放日期）公園，其中公園 ID 與外部查詢公園 ID 匹配，但是，外部查詢中的聯接返回所有公園，因此每個公園 ID 都會與子查詢進行比較，並且由於 Parks 表中的每個 Park 至少存在一次，所以它總是由子查詢返回。

如果要使用子查詢，可以使用 CROSS APPLY 代替，這將允許您的子查詢返回多於 1 行。然後，您可以使用 ROW_NUMBER() 僅標識每個城市所需的行：

SELECT * 
FROM Cities
CROSS APPLY(
 SELECT *, ROW_NUMBER() OVER (PARTITION BY CityId ORDER BY OpeningDate DESC) AS Row
 FROM Parks
) Parks
WHERE Parks.Row = 1
 AND Cities.Id = Parks.CityId

您可以在此處查看一個工作範例。

引用自：https://dba.stackexchange.com/questions/305349