Início / coding / Perguntas / 79436280
Accepted
PixelPaul
Asked: 2025-02-13 21:10:22 +0800 CST

T-SQL múltiplo COUNT() com JOIN e LEFT JOIN

  • 772

Tenho a seguinte consulta (que funciona e retorna o resultado esperado), mas ela está usando várias subconsultas para obtê-la COUNTe estou preocupado que a consulta seja muito ineficiente da forma como está escrita atualmente:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        (SELECT COUNT(*) FROM CarImage WHERE CarId = c.Id) AS ImageCount,
        (SELECT COUNT(*) FROM CarLike WHERE CarId = c.Id) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

Minhas tentativas iniciais foram sem usar as subconsultas para o COUNT, o que consegui fazer funcionar até adicionar o , LEFT JOINo que distorceu os resultados de ambas as contagens:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        COUNT(ci.CarId) AS ImageCount,
        COUNT(cl.CarId) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

Imagino que exista uma maneira de fazer a consulta nº 2 funcionar e que ela seja mais eficiente que a consulta nº 1?

sql-server

2 respostas

  1. Best Answer

    Basta remover os GROUP BYe também as junções, pois não são necessárias

    SELECT 
    c.Id                AS Id,
    cd.Make             AS Make,
    u.Id                AS UserId,
    (SELECT COUNT(*) FROM CarImage ci WHERE cl.CarId = c.Id) AS ImageCount,
    (SELECT COUNT(*) FROM CarLike cl WHERE ci.CarId = c.Id) AS LikeCount
FROM Car c
JOIN CarDetail cd   ON c.Id = cd.CarId
JOIN User u         ON c.UserId = u.Id
WHERE c.Status = 'Active';

    Se a junção interna CarImagefoi proposital, então você pode usar um APPLYe um predicado extra

    SELECT 
    c.Id                AS Id,
    cd.Make             AS Make,
    u.Id                AS UserId,
    i.ImageCount,
    (SELECT COUNT(*) FROM CarLike cl WHERE ci.CarId = c.Id) AS LikeCount
FROM Car c
JOIN CarDetail cd   ON c.Id = cd.CarId
JOIN User u         ON c.UserId = u.Id
CROSS APPLY (
    SELECT COUNT(*) AS ImageCount
    FROM CarImage ci
    WHERE cl.CarId = c.Id
) i
WHERE c.Status = 'Active'
  AND i.ImageCount > 0;

    Você também pode colocar um GROUP BY ()nisso APPLYe remover o predicado extra para obter o mesmo efeito de um INNER JOIN, embora isso possa ser uma mudança muito sutil que outros programadores podem não entender.

    CROSS APPLY (
    SELECT COUNT(*) AS ImageCount
    FROM CarImage ci
    WHERE cl.CarId = c.Id
    GROUP BY ()
) i
WHERE c.Status = 'Active';
    • 2

  2. Acho que a primeira coisa a ser notada é que subconsultas não são necessariamente ineficientes, correlacionadas ou não, então sua consulta de trabalho está potencialmente boa. O SQL Server inicialmente reescreverá sua subconsulta como um APPLY, mas então tentará reescrever seu apply como um join e o otimizador explorará os méritos relativos de ambos antes de escolher um plano. Então, na realidade, muitas vezes, quer você escreva um join ou uma subconsulta correlacionada, você obterá o mesmo plano e nenhuma abordagem é mais eficiente que a outra.

    Dito isso, o otimizador é melhor em reescrever uma junção como uma aplicação do que em reescrever uma aplicação como uma junção, o que pode significar que a única implementação física disponível é uma junção de loop aninhado; o que pode ser menos eficiente do que as alternativas disponíveis para uma junção. Como tal, se eu puder escrever uma consulta usando uma junção, então eu o farei, isso dá ao otimizador a melhor chance de escolher o melhor plano.

    Dessa forma, eu pessoalmente reescreveria sua consulta com junções, mas realizaria a agregação em subconsultas para evitar os produtos cartesianos que você está vendo e que estão impactando suas contagens:

    SELECT 
    c.Id                AS Id,
    cd.Make             AS Make,
    u.Id                AS UserId,
    ISNULL(ci.ImageCount, 0) AS ImageCount,
    ISNULL(ci.LikeCount, 0)  AS LikeCount
FROM Car c
JOIN CarDetail cd   ON c.Id = cd.CarId
JOIN [User] u       ON c.UserId = u.Id
LEFT JOIN 
(   SELECT  cl.CariId, COUNT(*) AS LikeCount
    FROM    CarLike cl 
    GROUP BY cl.CarId
) AS cl
    ON c.Id = cl.CarId
LEFT JOIN 
(   SELECT  ci.CariId, COUNT(*) AS ImageCount
    FROM    CarImage ci 
    GROUP BY ci.CarId
) AS ci
    ON c.Id = ci.CarId
WHERE c.Status = 'Active';

    NB Se você estiver realmente interessado em alguns dos detalhes do acima, Paul White escreveu sobre isso com muito mais detalhes do que eu poderia aqui: Apply versus Nested Loops Join

    • 2

relate perguntas