获取数据库中每个问题的分数

w6mmgewl  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(342)

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

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

答题表:

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

投票表:

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

答案表中的qid引用问题表中的qid,投票表中的oubid引用问题表中的qid或答案表中的rid。
我对sql有点陌生,在尝试了很多之后,我没有得到我想要的。
所以我想展示的是按分数排序的问题。问题表中每个问题的得分是该问题及其答案的最佳得分。得分是根据该问题或答案的赞成票和反对票之差来计算的。
例如,问题1的分数是2,因为问题1及其答案(2,3,7)的最佳分数是问题本身的分数,即3-1=2。或者举例来说,问题4的分数是1,因为这个问题的答案6的分数是1,这比其他答案和问题4本身要好。
结果是这样的:1,4,9。
目前,我只有一个代码,它只是显示每一个问题或答案的选票,它有。

select v.PubId, v.Type from
(select r.RId from Answer r
UNION 
SELECT q.QId from Question q
) as a 
left join Vote v on a.RId=v.PubId 
where v.PubId is not null
rxztt3cl

rxztt3cl1#

您的表结构是非常反直觉的,因为您显然是在问答表中共享一个主键列。
不过,你可以随心所欲 union all ,一个 left join ,以及两个聚合级别:

select qid, max(score) score
from (
    select x.qid, x.pubid, sum(type = 'P') - sum(type = 'N') score
    from (
        select qid, qid pubid from questions
        union all
        select qid, rid from answers
    ) x
    left join votes v on v.pubid = x.pubid
    group by x.qid, x.pubid
) t
group by qid
order by score desc

子查询计算每个问题和答案的分数,同时跟踪它们之间的关系。然后,外部查询选择每个问题的最高分数。
db小提琴演示:

qid | score
--: | ----:
  1 |     2
  4 |     1
  9 |  null

相关问题