mysql - 在MYSQL的where子句中使用图像计数

Question

我试图创建一个 MYSQL 查询来选择图像数量大于 0 的最新 10 条广告记录。问题是我收到错误“'where 子句'中的未知列'num_photos'”，因为 where 语句在计数之前执行。我该如何重写这个 mysql 查询来解决这个问题？

    
SELECT adverts.*,
( SELECT advert_images.imagethumbpath FROM advert_images WHERE advert_images.advert_id = adverts.advert_id  ORDER BY image_id ASC LIMIT 1 ) as imagethumbpath,
( SELECT count(advert_images.advert_id) from advert_images WHERE advert_images.advert_id = adverts.advert_id ) AS num_photos
FROM adverts
WHERE num_photos > 0
ORDER BY date_updated DESC 
LIMIT 10

Answer 1

您可以使用 HAVING 而不是 WHERE 来完成此操作:

SELECT adverts.*,
( SELECT advert_images.imagethumbpath FROM advert_images WHERE advert_images.advert_id = adverts.advert_id  ORDER BY image_id ASC LIMIT 1 ) as imagethumbpath,
( SELECT count(advert_images.advert_id) from advert_images WHERE advert_images.advert_id = adverts.advert_id ) AS num_photos
FROM adverts
HAVING num_photos > 0
ORDER BY date_updated DESC 
LIMIT 10