如何按特定列对表进行汇总和分组 [英] How to sum and group a table by a specific column

问题描述

我有一个这样的数据库:
问题表:

I have a database like this:
Question table:

QId | Title
----------
1   | SR
4   | TR
9   | AA

答案表:

RId | QId
----------
2   | 1
3   | 1
5   | 4
6   | 4
7   | 1
8   | 4

投票表:

PubId | Type
-------------
2     | P
3     | N
3     | N
1     | P
1     | N
5     | P
4     | P
2     | N
2     | P

我想计算每个问题的分数.一个问题的分数是对该问题及其所有答案的赞成与反对票数之差.例如，QId为1的问题1(答案分别为2、3和7)的分数是-1，因为它具有3个正面投票和4个负面投票.
到目前为止，我只能计算给定问题的分数.代码是:

I want to calculate the score of every question. The score of a question is the difference between positive and negative votes on that question and all of its answers. For example the score of question 1 (with answers as 2, 3 and 7) with QId of 1 is -1 because it hase 3 positive votes and 4 negative votes.
So far I can only calculate the score of a given question. The code is:

SELECT P+N FROM (
SELECT sum (case WHEN Type='P' then +1 else 0 end) as P,
sum (case WHEN Type='N' then -1 else 0 end) as N
from ( 
SELECT v.Type from Vote v where v.PubId in (
SELECT r.RId FROM Answer r WHERE r.QId=4
UNION
select q.QId from Question q where q.QId=4)
)
)

如何计算每个问题的分数，然后从得分最高的问题到得分最低的问题进行排序?

How can I calculate the score of every question and then sort them from the question with the highest score to the question with lowest score?

我想要的结果是4(分数为+2)，9(分数为0)，1(分数为-1).

The result that I want is 4 (with the score of +2), 9 (with the score of 0), 1 (with the score of -1).

推荐答案

您可以结合使用LEFT JOIN和CASE来计算分数.例如:

You can use LEFT JOINs combined with CASE to compute the scores. For example:

select
  *
from (
  select
    q.qid,
    sum(case when v.type = 'P' then 1 
             when v.type = 'N' then -1 else 0 end
       ) as score
  from question q
  left join answer a on a.qid = q.qid
  left join vote v on v.pubid = a.rid
  group by q.qid
) x
order by score desc

这篇关于如何按特定列对表进行汇总和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！