我需要一个来自两个表Car
和CarImage
的SELECT查询.每辆车都会有多个图像.我只想返回每辆车的第一个图像(DisplayOrder = 1
)的总图像计数和图像名称,但是添加这个WHERE子句会导致图像计数始终等于1.如何仅返回每辆车的第一个图像的总图像计数和图像名称?
我的简化查询如下所示:
SELECT c.Id,
c.Make,
c.Model,
ci.ImageName,
COUNT(ci.ImageName) AS ImageCount
FROM Car c
JOIN CarImage ci
ON c.Id = ci.CarId
WHERE ci.DisplayOrder = 1
GROUP BY (ci.ImageName) <-- usually need some kind of `GROUP BY` to get count