主页 / dba / 问题 / 305349
Accepted
HeyJude
Asked: 2021-12-24 12:07:33 +0800 CST

了解为什么在使用子查询过滤器时让每个父行与其最新的子行加入不起作用

  • 772

我有一个父子表关系，我试图让每个父行及其最新的子行详细信息。

我尝试使用子查询来做到这一点没有奏效，我只能使用 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
);
sql-server join

1 个回答

  1. Best Answer
    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

    您可以在此处查看一个工作示例。

    • 1

相关问题