Início / dba / Perguntas / 305349
Accepted
HeyJude
Asked: 2021-12-24 12:07:33 +0800 CST

Entendendo por que juntar cada linha pai com seu filho mais recente não está funcionando ao usar o filtro de subconsulta

  • 772

Eu tenho um relacionamento de tabelas pai-filho e estava tentando obter cada linha pai com os detalhes mais recentes da linha filho.

Minhas tentativas de fazer isso usando uma subconsulta não funcionaram, e eu só consegui fazer isso usando um CTE.

gostaria de entender:

  1. Por que a maneira de subconsulta não funciona?
  2. Existe uma maneira de corrigi-lo?

Aqui está um exemplo de brinquedo do relacionamento pai-filho ( demo completa está aqui ):

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)
    );

Consulta bem-sucedida usando um 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;

Tentativas falhas:

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 respostas

  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
);

    Nesta consulta, a subconsulta busca o Park mais recente (com base na data de abertura) da tabela Parks, onde o Park Id corresponde ao Park Id da consulta externa. get comparado com a subconsulta e, como cada Park na tabela Parks existe pelo menos uma vez, ele sempre é retornado pela subconsulta.

    Se você quiser usar uma subconsulta, poderá usar CROSS APPLY, o que permitirá que sua subconsulta retorne mais de 1 linha. Você pode então usar ROW_NUMBER() para identificar apenas a linha desejada por cidade:

    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

    Você pode ver um exemplo de trabalho aqui .

    • 1

relate perguntas