mysql - LEFT JOIN 即使空 SQL

Question

我的数据库中有两个表:意见和投票

一个建议可以有 0 个或多个投票

我希望所有的建议都按臭名昭著的顺序进行验证:

SELECT advices.*, COUNT(upvotes.id) - COUNT(downvotes.id) AS notoriety 
FROM `advices` 
LEFT JOIN votes AS upvotes ON upvotes.is_good=1 AND upvotes.advice_id=advices.id 
LEFT JOIN votes AS downvotes ON downvotes.is_good=0 AND downvotes.advice_id=advices.id 
WHERE `advices`.`subject_id` = 1
AND `advices`.`state` = 'validated' 
ORDER BY notoriety ASC

但是，结果只显示有投票的建议!我应该改变什么才能在没有投票的情况下获得建议？

谢谢

Answer 1

使用条件聚合而不是两个连接:

SELECT a.*, 
       (SUM(downvotes.is_good = 1) - SUM(downvotes.is_good = 0) ) AS notoriety 
FROM advices a LEFT JOIN
     votes v
     ON a.id = v.advice_id
WHERE a.`subject_id` = 1 AND a.`state` = 'validated' 
GROUP BY a.id
ORDER BY notoriety ASC;

您可以使用 count(distinct) 而不是 count() 让您的版本正常工作。但是，上面的版本更简单，性能应该更好。