2 组 SQL 查询

Question

这是一个使用 MySql 5.0.51 执行没有问题的查询:

SELECT cc_split.idSplit,
       count( voteup.vote),
       count( votedown.vote)
FROM cc_split
LEFT JOIN cc_split_usager AS voteup ON voteup.idSplit = cc_split.idSplit
      AND voteup.vote >0
LEFT JOIN cc_split_usager AS votedown ON votedown.idSplit = cc_split.idSplit
      AND votedown.vote <0
WHERE cc_split.isArchived = false
GROUP BY cc_split.idSplit
LIMIT 0, 30

问题在于 COUNT 没有显示我想要的内容。您是否可以看到，它采用 cc_ split_ userer 并应该计算积极的投票数量，另一方面计算消极的投票数量。上面的代码显示了 2 个赞成票和 2 个反对票，而实际上它应该是 2 个赞成票和 1 个反对票。在单个 SQL 查询中获得赞成票和反对票数的技巧是什么。

表格如下所示:

cc_split:
-idSplit
-...
cc_split_usager:
-idSplit
-vote (can be +1 or -1)
-...

有什么提示吗？

Answer 1

尝试:

SELECT s.idSplit, 
    count(case when v.vote > 0 then 1 else null end) as VoteUp, 
    count(case when v.vote < 0 then 1 else null end) as VoteDown 
FROM cc_split s
LEFT JOIN cc_split_usager AS v ON v.idSplit = s.idSplit
WHERE s.isArchived = false
GROUP BY s.idSplit
LIMIT 0, 30