MySQL查询—根据列获取计数的总和

I have a mysql table with actions and question_ids. Each action comes with a score like this:

我有一个包含动作和question_id的mysql表。每个动作都有这样的分数:

     ACTION       | SCORE
downvote_question |  -1
upvote_question   |  +1
in_cardbox        |  +2

I want to query for the question with the highest score but I can't figure it out.

我想查询分数最高的问题，但我搞不清楚。

http://sqlfiddle.com/#!2/84e26/15

http://sqlfiddle.com/ ! 2/84e26/15

So far my query is:

到目前为止，我的问题是:

SELECT count(*), l1.question_id, l1.action 
FROM `log` l1
GROUP BY l1.question_id, l1.action

which gives me every question_id with all its accumulated actions.

它给了我所有的question_id和它累积的动作。

What I want is this:

我想要的是:

QUESTION_ID | SCORE
     2      |   5
     1      |   4
     3      |   1
     4      |   1
     5      |   1

I can't figure it out - I probably need subqueries, JOINS or UNIONS...

我想不出来——我可能需要子查询、连接或结合……

3 个解决方案

#1

You should replace count(*) with sum(l1.score) because sum will add all values based on group by statement

您应该用sum(l1.score)替换count(*)，因为sum将根据group by语句添加所有值

SELECT sum(l1.score), l1.question_id, l1.action 
FROM `log` l1
GROUP BY l1.question_id, l1.action

With constant scores works on SQL Fiddle (with grouping by question):

在SQL Fiddle是常量分数(根据问题分组):

SELECT 
sum(
  CASE WHEN l1.action = 'downvote_question' THEN -1 
  WHEN l1.action = 'upvote_question' THEN 1 
  ELSE 2 END
) score,
l1.question_id
FROM `log` l1
GROUP BY l1.question_id

#2

Maybe you can try this one.

也许你可以试试这个。

SELECT  a.question_id, sum(b.score) totalScore
FROM   `log` a INNER JOIN scoreValue b
          on a.`action` = b.actionname
group by a.question_id
ORDER BY totalScore DESC

SQLFiddle Demo

#3

SELECT sum(SCORE), l1.question_id, l1.action 
FROM `log` l1
GROUP BY l1.question_id, l1.action

is it what you want to?

这是你想要的吗?

upd: in your code on fidel, there is no such column as score, but i think it wont be a problem to create a tabel with action | score and join it to sum(score)

upd:在您的关于fidel的代码中，没有诸如score这样的列，但是我认为创建一个带有action | score的表并将其加入sum(score)不是问题