计票的正负数：

| 我有以下表格：

post (id, title, content) etc
author (id, username) etc
author_vote (post_id, author_id, value)

值是tiny_int，可以为1或-1。我想计算每个帖子的赞成票和反对票数：

$posts = sql_select($link, \"SELECT post.*, author.username 
                            FROM post, author 
                            AND author.id = post.author_id\");

为什么以下代码不起作用？

array_walk($posts, function(&$post, $link){
   $post[\'positive\'] = sql_select($link, \"SELECT SUM(value) FROM author_vote WHERE post_id = $post[\'id\']
                                            AND value  = 1\");

   $post[\'negative\'] = abs(sql_select($link, \"SELECT SUM(value) FROM author_vote WHERE post_id = $post[\'id\']
                                            AND value  = -1\"));
});

我还尝试了以下操作，这会使每个帖子的所有投票都相同：

foreach ($posts as &$post)
{
   $id = $post[\'id\'];
   $post[\'positive\'] = (int)sql_select($link, \"SELECT SUM(value) FROM author_vote WHERE post_id = $id
                                           AND value  = 1\");
   $post[\'negative\'] = (int)abs(sql_select($link, \"SELECT SUM(value) FROM author_vote WHERE post_id = $id
                                               AND value  = -1\"));
}

还有什么方法可以做到，而不必为每个帖子多次查询数据库？（mem）如何缓存不断变化的内容（例如这样）？

已邀请:

2 个回复

郸身

您可以在一个查询中进行计数：

Select Sum( Case When value < 0 Then 1 Else 0 End ) As NegVotes
    , Sum( Case When value > 0 Then 1 Else 0 End ) As PosVotes
From author_vote
Where post_id = ...

如果您希望每个帖子的正面和负面投票：

Select post_id
    , Sum( Case When value < 0 Then 1 Else 0 End ) As NegVotes
    , Sum( Case When value > 0 Then 1 Else 0 End ) As PosVotes
From author_vote
Group By post_id

如果您想将第一个查询和第二个查询结合起来，则可以获得：

Select post....
    , author.username 
    , Coalesce(post_count.NegVotes,0) As NegVotes
    , Coalesce(post_count.PosVotes,0) As PosVotes
From post
    Join author
        On author.id = post.author_id
    Left Join   (
                Select post_id
                    , Sum( Case When value < 0 Then 1 Else 0 End ) As NegVotes
                    , Sum( Case When value > 0 Then 1 Else 0 End ) As PosVotes
                From author_vote
                Group By post_id
                ) As post_count
        On post_count.post_id = post.post_id

辟官陡板休

我找不到您正在使用的函数ѭ7but，但是您最好在SQL中使用count(*)，而不要尝试ѭ9。您只需要计算看起来像的行，而不是对值求和。您也可以放心一点，并使用GROUP BY：

SELECT value, count(value) AS value_count FROM author_vote WHERE post_id = $id GROUP BY value

每个唯一值将返回一行。返回的每一行将报告唯一值以及使用该值的行数。

要回复问题请先登录或注册

计票的正负数：

2 个回复

发起人

php

mysql

voting

问题状态

计票的正负数：

与内容相关的链接

2 个回复

发起人

php

mysql

voting

问题状态