douxiong4250
2009-11-21 23:41
浏览 45
已采纳

从一个表中选择并包含另一个的总和值

My title is terrible but I'm having a hard time wrapping my head around this. I have two tables. Links and Votes. Links contain info about links submitted and the usual jazz, votes contains a foreign key link_id and a karma_up and karma_down value (both unsigned ints). Because I want to limit votes to one per user and record vote time I opted for the two tables method. What I want to do though is display the sum'ed karma of a link. I need to be able to grab all the links and then their one to many votes which are sum'ed up SUM(karma_up - karma_down). All I have been able to do is SUM up the entire column which doesn't do me any good.

Hopefully someone just "gets" what I'm trying to do but till then I will be updating this post with as much relevant information as possible.

UPDATE For anyone interested, my final query is

SELECT links.*, (SUM(karma_up) - SUM(karma_down)) AS karma
FROM links, votes
WHERE links.id = votes.link_id
GROUP BY votes.link_id

图片转代码服务由CSDN问答提供 功能建议

我的头衔太可怕了,但我很难绕过这个。 我有两张桌子。 链接和投票。 链接包含有关提交的链接和通常的爵士乐的信息,投票包含外键link_id以及karma_up和karma_down值(均为无符号整数)。 因为我想限制每个用户一票并记录投票时间,所以我选择了两个表方法。 我想做的是显示链接的总和业力。 我需要能够抓住所有的链接然后他们的一对多票数总结为SUM(karma_up - karma_down)。 我所能做的就是整理整个专栏,这对我没有任何帮助。

希望有人只是“得到”我想要做的事但直到那时我 将使用尽可能多的相关信息更新此帖子。

更新 对于任何感兴趣的人,我的最终查询是 < pre> SELECT links。*,(SUM(karma_up) - SUM(karma_down))AS karma FROM links,votes WHERE links.id = votes.link_id GROUP BY votes.link_id < / pre>

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • dqiatvbi61502 2009-11-21 23:45
    已采纳

    You could post your schema to be sure, but something like this should work

    select v.link_id, (sum(karma_up) - sum(karma_down)) as points from 
    Links l, Votes v
    where l.link_id = v.link_id group by v.link_id
    

    That should give you the points per link_id.

    打赏 评论
  • doucai9270 2009-11-21 23:46

    You need to do something like this:

    SELECT Links.LinkId, SUM(Votes.karma_up) - SUM(Votes.karma_down) AS karma FROM Links
        LEFT JOIN Votes ON Links.LinkId = Votes.LinkId
        GROUP BY Links.LinkId
    

    Note the use of LEFT JOIN to ensure that links without votes are also included.

    打赏 评论
  • dougan6402 2009-11-21 23:48

    Points per link id, and zero karma if there are no votes:

    select l.*, coalesce(sub_karma, 0) as karma
    from links as l
        left join (
            select link_id, sum(karma_up - karma_down) as sub_karma
            from votes
            group by link_id
        ) as v using (link_id)
    ;
    
    打赏 评论
  • dongluan2612 2009-11-22 00:13

    Just one more thing I'd like to mention. Unless a vote is allowed to have both up and down karma at the same time (unlikely from the desription), you would better off replacing them karma_up and karma_down with a single karma_delta column. Then set it to -1 or 1 depending on whether it's down or up respectively.

    This would simplify your query even more:

    SELECT links.*, SUM(karma_delta) AS karma
    FROM links, votes
    WHERE links.id = votes.link_id
    GROUP BY votes.link_id
    
    打赏 评论

相关推荐 更多相似问题