是否有可能在此查询中获得overall_score的总和?
问题描述:
所以我有一个链接表和这些链接的投票表。因此,例如,链接google.co.uk在链接表中有一条记录,然后在投票表中可以有四个投票记录,每个记录的得分为-1,1或2。我已经完成了对每个链接的投票进行总结的这个查询,但是有可能将同一个查询中的所有链接的所有得分相加,所以用户有一个得分?是否有可能在此查询中获得overall_score的总和?
SELECT *, SUM(v.vote_score) AS overall_score
FROM mdl_link_critic_links l
JOIN mdl_link_critic_votes v on l.id = v.link_id
WHERE l.user_id = '3'
GROUP BY l.id
结果是一样
google.co.uk, user_id 3, overall_score 3
ebay.co.uk , user_id 3, overall_score 4
stackoverflow.com, user_id, 3, overall_score 10
,我想总结所有的总分,所以在这种情况下,这将是17
答
什么子查询?它看起来像这样或多或少:
SELECT x.user_id, SUM(overall_score) AS user_score
FROM (
SELECT *, SUM(v.vote_score) AS overall_score
FROM mdl_link_critic_links l
JOIN mdl_link_critic_votes v on l.id = v.link_id
GROUP BY l.id
) AS x
GROUP BY x.user_id;
答
SELECT l.id, l.name, v.user_id, v.vote_score AS user_score, o.overall_score
FROM mdl_link_critic_links l
JOIN mdl_link_critic_votes v ON v.link_id = l.id
JOIN (SELECT v.link_id, SUM(v.vote_score) AS overall_score
FROM mdl_link_critic_votes v
GROUP BY v.link_id) o ON o.link_id = l.id
* “所以在这种情况下,这将是17” * - 你的意思是16. 3,3,3,4和3让16,不17. ;-) –
不,总和领域overall_score,3 + 4 + 10使17 :) – Rich
'WITH ROLLUP'它会给你null,null,17' – splash58