在两个子查询的计数之间求差 [英] Getting difference between counts of two subqueries

问题描述

我正在尝试通过运行SELECT (SELECT COUNT(vote_id) AS vote_up FROM votes WHERE vote='UP'),(SELECT COUNT(vote_id) AS vote_down FROM votes WHERE vote='DOWN'),(vote_up - vote_down AS vote_score)来查找MYSQL中收到的增票和减票数之间的差，从而确定条目的得分.但是，当我尝试运行此命令时，它告诉我我没有正确的语法.我究竟做错了什么?

I'm trying to determine the score of an entry by finding the difference between the number of upvotes and downvotes it has received in MYSQL by running SELECT (SELECT COUNT(vote_id) AS vote_up FROMvotesWHERE vote='UP'),(SELECT COUNT(vote_id) AS vote_down FROMvotesWHERE vote='DOWN'),(vote_up - vote_down AS vote_score). When I try to run this though, it tells me that I do not have proper syntax. What am I doing wrong?

还有，还有更好的方法可以写这个吗?

Also, is there a better way to write this?

最后，找到具有最高和最低投票数的项目的理想方法是什么?我会ORDER BY [above query]吗?

And finally, what is the ideal way to find the item with the highest and lowest number of votes? Would I just ORDER BY [above query]?

推荐答案

您可以使用

SELECT some_id
  , SUM(
      CASE
        WHEN vote = 'UP'
        THEN 1
        WHEN vote = 'DOWN'
        THEN -1
        ELSE 0
      END
    ) as vote_score
FROM votes
GROUP BY some_id 

请注意，更好的方法是将+1或-1存储在投票中，然后您可以执行以下操作:

Note that the better approach is to have +1 or -1 stored in vote, then you can just do:

SELECT some_id, SUM(vote) as vote_score
FROM votes
GROUP BY some_id

顺便说一句，如果我的格式对您来说很奇怪，我在 http中进行了解释://bentilly.blogspot.com/2011/02/sql-formatting-style.html .

BTW if my formatting looks odd to you, I explained it in http://bentilly.blogspot.com/2011/02/sql-formatting-style.html.

这篇关于在两个子查询的计数之间求差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！