php - 如何显示用户声誉

我想在我的项目senior writer和junior writer中获得像徽章这样的声誉。基于获得的总数的用户信誉。

我有3张桌子：

表格发布：

    id_post | news      | id_user
    3       | IT news   | 1
    4       | game news | 2

表用户：

id_user | username
   1    | dora
   2    | boots
   3    | swipper

票数：

id_vote | id_post | id_user | LIKE
10      | 3       | 2       | 1
11      | 3       | 1       | 1
12      | 4       | 3       | 1

这是我的查询：

SELECT p.*, SUM(like) AS like_post,
(SELECT SUM(like) //this is subquery start
FROM user u
LEFT JOIN post p ON p.id_user= u.id_user
LEFT JOIN vote v ON  v.id_post=p.id_post GROUP BY u.id) AS reputation // end subquery
FROM post p
LEFT join user u ON p.id_user=u.id_user
LEFT JOIN vote v ON p.id_post=v.id_post
GROUP BY p.id_post
ORDER BY p.id_post DESC
LIMIT 10;

这是我的看法：

    <?php foreach $news as $data:?>
    <?php echo $data['id_post'];?>
    <?php echo $data['title'];?>
    LIKE: <?php echo $data['like_post']; ?>
    post by <?php echo $data['username'];?>
    reputation: <?php if ($data['reputation']==1)
    {echo "JUNIOR writer";}
      else
    {echo "SENIOR Writer"; }
    ?>
    <?php endforeach;?>

我的期望用户Dora因为获得2个LIKE而将获得名为senior writer的声誉。靴子将获得称为junior writer的声誉，因为仅获得1赞。

问题是返回查询是这样的错误：
Subquery returns more than 1 row

有答案吗？

非常感谢...

最佳答案

使用此查询。

SELECT u.id_user, u.username, SUM(v.like) AS reputation
FROM user u
LEFT JOIN post p ON u.id_user=p.id_user
LEFT JOIN vote v ON v.id_post=p.id_post
GROUP BY u.id_user

使用此php代码。

LIKE: <?php echo $data['reputation']; ?>
    post by <?php echo $data['username'];?>
    reputation: <?php if ($data['reputation']==1)
    {echo "JUNIOR writer";}
      else
    {echo "SENIOR Writer"; }
    ?>
    <?php endforeach;?>

编辑：**

查询like_post

SELECT v.id_user,v.id_post,SUM(v.like1) AS like_post
FROM vote v
LEFT JOIN USER u ON v.id_user=u.id_user
GROUP BY u.id_user

查询reputation

SELECT u.id_user, u.username, SUM(v.like1) AS reputation
FROM USER u
LEFT JOIN post n ON u.id_user=n.id_user
LEFT JOIN vote v ON v.id_post=n.id_post
GROUP BY u.id_user

关于php - 如何显示用户声誉，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27982257/