MySQL和负数和正数的和，没有子查询

I have two tables as follows:

我有以下两张表:

tbl_answers : id, (FK)authorID ...

tbl_answer_votes : id, (FK)voterID, (FK)answerID, vote (sInt, only ever -1 or 1)

tbl_answers has a one to many relationship with tbl_answer_votes and a vote can be either negative 1 or positive 1.

tbl_answers与tbl_answer_votes有一到多的关系，投票可以是- 1，也可以是- 1。

I need to join tbl_answer_votes on to tbl_answers but produce the sum of all negative votes for that answer and the sum of all positive votes for the answer as two separate columns.

我需要将tbl_answer_votes加入到tbl_answers，但要生成该答案的所有反对票之和，以及作为两个独立列的所有正数赞成票之和。

I can do this by sub-querying but as it will be a small part of a larger query and after reading the pitfalls on sub-queries I would like to try and produce those two negative and positive sums from the votes table as efficiently as possible.

我可以通过子查询来实现这一点，但由于它将是一个更大的查询的一小部分，并且在阅读了子查询的陷阱之后，我希望尽可能高效地从选票表中生成这两个负的和正的数字。

3 个解决方案

#1

You can use a CASE and a JOIN to accomplish this:

您可以使用一个CASE和一个JOIN来完成以下操作:

SELECT 
  a.id, 
  SUM(CASE v.vote when 1 then 1 else 0 end) as UpVotes,
  SUM(CASE v.vote when -1 then 1 else 0 end) as DownVotes
FROM 
  tbl_answers a
INNER JOIN 
  tbl_answer_votes v
ON 
  v.id = a.id
GROUP BY 
  a.id

This returns the ID from tbl_answers and the two columns with the total votes of either value from tbl_answer_votes. You didn't specify which (if any) other columns you'd want from either table, so you may have to adjust the column list in the SELECT and the GROUP BY portions to add additional columns.

这将返回来自tbl_answers的ID和包含来自tbl_answer_votes任何值的总投票数的两列。您没有指定要从这两个表中选择哪个(如果有的话)列，因此您可能需要按部分调整SELECT和GROUP中的列列表以添加其他列。

#2

Sorry i dont have time. but here you might have to twick it little bit.

对不起，我没有时间。但是这里你可能要稍微转动一下。

 select a.id, SUM(CASE WHEN v.vote = 1 Then 1 ELSE 0 END ), SUM(CASE WHEN (v.vote = -1) then 1 ELSE 0 end)
from tbl_answers a
inner join tbl_answer_votes v
    On a.id = v.asnwerId
where a.id = 123
group by a.id

#3

Write a query that returns the sum of the votes, and the count of the votes for each answer.

编写一个查询，返回投票的总和，以及每个答案的投票数。

Since

自

sum of votes = positive votes - negative votes
count of votes = positive votes + negative votes

you have

你有

positive votes = (count+sum)/2
negative votes = (count-sum)/2

so just add those expressions to your query. Something like

因此只需将这些表达式添加到您的查询中。类似的

SELECT answerID, (COUNT(*)+SUM(vote))/2 as upvotes,
                 (COUNT(*)-SUM(vote))/2 as downvotes
FROM tbl_answer_votes
GROUP BY answerID

and then you should be OK for the join part.

然后加入部分就没问题了。

#1