php - 仅返回在 MYSQL 查询中找到的记录数

Question

我有以下 MYSQL 查询，它返回为每条记录找到的照片数量，其中照片数量大于 0。

SELECT advert_id, (SELECT COUNT( * ) FROM advert_images b WHERE b.advert_id = adverts.advert_id) AS num_photos
FROM adverts
WHERE adverts.approve =  '1'
HAVING num_photos > 0

查询工作正常，但我只想返回找到的记录数。即至少有一张照片的记录数。我试图将整个查询包装在一个 COUNT 中，但它给出了一个错误。我想在查询中执行此操作，而不是在 php 中找到单独的记录计数。

Answer 1

SELECT COUNT(*) AS TotalRecords 
FROM 
(
  SELECT a.advert_id, COUNT(*) AS num_photos
  FROM adverts AS a
    JOIN advert_images AS i
    ON i.advert_id = a.advert_id
  WHERE a.approve =  '1'
  GROUP BY a.advert_id
  HAVING num_photos > 0
) AS mq