我在MySQL中有两个表，即主题和照片，我希望计算每个主题的照片数量
SELECT a.id, a.name, count(a.id) as `refcount`, FROM `subjects` a LEFT JOIN `photos` b ON (a.id = b.subject_id) GROUP by a.id ORDER BY a.name";
即使rowcount（）=0，也返回1。怎么修我尝试了各种MySql语法，包括count（field），但都是徒劳
uyto3xhc1#
您需要count photos.id（b.id），如果没有找到给定主题的照片，查询将返回null，count（null）= 0。
count
SELECT a.id, a.name, count(b.id) as `refcount` FROM `subjects` a LEFT JOIN `photos` b ON a.id = b.subject_id GROUP by a.id, a.name ORDER BY a.name;
hlswsv352#
您可以使用以下命令：SELECT a.id, a.name, SUM(IF(b.id IS NULL, 0, 1)) as refcount, FROM subjects a LEFT JOIN photos b ON (a.id = b.subject_id) GROUP by a.id order by a.name。PS：我没试过
SELECT a.id, a.name, SUM(IF(b.id IS NULL, 0, 1)) as refcount, FROM subjects a LEFT JOIN photos b ON (a.id = b.subject_id) GROUP by a.id order by a.name
2条答案
按热度按时间uyto3xhc1#
您需要
countphotos.id（b.id），如果没有找到给定主题的照片，查询将返回null，count（null）= 0。hlswsv352#
您可以使用以下命令：
SELECT a.id, a.name, SUM(IF(b.id IS NULL, 0, 1)) as refcount, FROM subjects a LEFT JOIN photos b ON (a.id = b.subject_id) GROUP by a.id order by a.name。PS：我没试过