SQL SUM和COUNT返回错误的值

问题描述：

我发现了一堆类似的问题，但对我没有任何帮助，或者我太愚蠢，无法正确地进行操作.如果我使用 COUNT(DISTINCT visits.id)，则访问计数效果很好，但是投票计数却完全错误-它显示的值比应有的大小大3到4倍.

I found a bunch of similar questions but nothing worked for me, or I am too stupid to get how to do it right. The visit count works fine if I use COUNT(DISTINCT visits.id) but then the vote count goes totally wrong - it displays a value 3 to 4 times larger than it should be.

这就是查询

SELECT SUM(votes.rating), COUNT(visits.id)
FROM topics
LEFT JOIN visits ON ( visits.content_id = topics.id )
LEFT JOIN votes ON ( votes.content_id = topics.id )
WHERE topics.id='1'
GROUP BY topics.id

投票表如下所示:

id int(11) | rating tinyint(4) | content_id int(11) | uid int(11)

访问表

id int(11) | content_id int(11) | uid int(11)

主题表

id int(11) | name varchar(128) | message varchar(512) | uid int(11)

帮助?

答

基本上，您是在累加或计数可能返回的总行数.因此，如果每个ID有3次访问和4次投票，则访问次数将乘以4，而投票将乘以3.

Basically, you're summing or counting the total number of rows potentially returned. So, if there are three visits and four votes for each id, then the visits will be multiplied by four and the votes by three.

我认为，使用子查询可以最轻松地实现您想要的内容:

I think what you want can easiest be ackomplished by using subqueries:

SELECT (SELECT SUM(v.rating) FROM votes v WHERE v.content_id = t.id), 
       (SELECT COUNT(vi.id) FROM visits vi WHERE vi.content_id = t.id)
FROM topics t
WHERE t.id=1 
GROUP BY t.id

SQL SUM和COUNT返回错误的值

相关推荐