如何查询每辆车的总图片数及首张图片名称？解决计数异常问题

问题出在你用WHERE ci.DisplayOrder = 1过滤了所有图片记录，导致最终只能统计到每辆车的首张图片，计数自然为1。要同时获取总图片数和首张图片，需要分别处理这两个需求，再将结果关联。

方法一：子查询关联法 #

SELECT 
    c.Id,
    c.Make,
    c.Model,
    first_image.ImageName AS FirstImageName,
    image_count.TotalImages
FROM Car c
-- 关联子查询获取每辆车的总图片数
LEFT JOIN (
    SELECT CarId, COUNT(*) AS TotalImages
    FROM CarImage
    GROUP BY CarId
) image_count ON c.Id = image_count.CarId
-- 关联子查询获取每辆车的首张图片
LEFT JOIN (
    SELECT CarId, ImageName
    FROM CarImage
    WHERE DisplayOrder = 1
) first_image ON c.Id = first_image.CarId

方法二：条件聚合+分组法 #

SELECT 
    c.Id,
    c.Make,
    c.Model,
    -- 通过条件聚合筛选首张图片
    MAX(CASE WHEN ci.DisplayOrder = 1 THEN ci.ImageName END) AS FirstImageName,
    -- 统计当前车辆的所有图片数量
    COUNT(ci.Id) AS TotalImages
FROM Car c
LEFT JOIN CarImage ci ON c.Id = ci.CarId
GROUP BY c.Id, c.Make, c.Model

方法三：窗口函数法（适用于MySQL 8+、PostgreSQL、SQL Server等） #

SELECT DISTINCT
    c.Id,
    c.Make,
    c.Model,
    -- 窗口函数筛选当前车辆的首张图片
    MAX(CASE WHEN ci.DisplayOrder = 1 THEN ci.ImageName END) OVER (PARTITION BY c.Id) AS FirstImageName,
    -- 窗口函数统计当前车辆的总图片数
    COUNT(ci.Id) OVER (PARTITION BY c.Id) AS TotalImages
FROM Car c
LEFT JOIN CarImage ci ON c.Id = ci.CarId

核心说明 #

• 避免用WHERE ci.DisplayOrder = 1全局过滤，否则会丢失车辆的其他图片数据，导致计数错误。
• 总图片数需要基于所有CarImage记录按CarId统计，首张图片则单独筛选DisplayOrder = 1的记录获取。

内容的提问来源于stack exchange，提问作者PixelPaul