我的数据库中有两个表:建议和投票
一个建议可以有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
但是,结果仅显示带有投票的建议!我也应该更改以得到未经表决的建议吗?
谢谢
使用条件聚合而不是两个联接:
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()
。但是,以上版本更简单,并且应具有更好的性能。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句