主页 / coding / 问题 / 79436280
Accepted
PixelPaul
Asked: 2025-02-13 21:10:22 +0800 CST

T-SQL 使用 JOIN 和 LEFT JOIN 进行多个 COUNT()

  • 772

我有以下查询（它确实有效并返回预期结果）但它使用多个子查询来获取COUNT并且我担心查询会非常低效，因为它目前是这样写的：

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

我最初的尝试是没有使用 的子查询COUNT，它能够正常工作，直到我添加了 ，LEFT JOIN这扭曲了两个计数的结果：

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

我猜测有一种方法可以使查询 #2 正常工作，并且它比查询 #1 更有效率？

sql-server

2 个回答

  1. Best Answer

    只需删除GROUP BY以及连接，因为它们不是必需的

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

    如果内连接CarImage是故意的，那么您可以使用一个APPLY和额外的谓词

    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;

    您还可以将 放入GROUP BY ()其中APPLY并删除多余的谓词以获得与 相同的效果INNER JOIN，尽管这可能是一个非常微妙的变化，其他程序员可能无法理解。

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

  2. 我认为首先要注意的是，子查询不一定是高效的，无论是否相关，因此您的工作查询可能没问题。 SQL Server 最初会将您的子查询重写为APPLY，但随后它会尝试将您的应用重写为连接，优化器会在选择计划之前探索两者的相对优点。 因此，实际上很多时候无论您编写的是连接还是相关子查询，您都会得到相同的计划，而且两种方法都没有比另一种更有效。

    话虽如此，优化器更擅长将连接重写为应用，而不是将应用重写为连接，这可能意味着唯一可用的物理实现是嵌套循环连接；这可能比连接的替代方案效率更低。因此，如果我可以使用连接编写查询，那么我会这样做，这为优化器提供了选择最佳计划的最佳机会。

    因此，我个人会用连接重写您的查询，但在子查询中执行聚合，以避免您看到的笛卡尔积影响您的计数：

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

    注意：如果你真的对上面的一些细节感兴趣，Paul White 对此的描述比我在这里写的更详细：Apply versus Nested Loops Join

    • 2

相关问题