php - 对具体回复进行计票

Question

我有一个保存投票记录的表格

条件

1. 回复可以有很多票；

2. 用户可以对特定回复 ID 投票一次。

   表格:reply_vote

       +----+-------------+-------------+   
       | id |   reply_id  | userID      | 
       +----+-------------+----+--------+   
       | 1  | 23          | 21          | 
       | 3  | 33          | 21          | 
       | 4  | 23          | 25          | 
       | 5  | 23          | 12          | 
   

我想显示每个回复的总票数

例如

reply_id 23 has 3 votes with userid (21,25,12)

reply_id 33 has 1 vote with userid (21)

我不想在 php 中使用 while 循环获取所有数据

到目前为止，我已经使用了下面的查询，但它在页面上显示了所有投票，这不是所需要的

SELECT
  reply.id,
  reply.reply,
  COUNT(reply_vote.id) AS likes
FROM reply
  LEFT JOIN reply_vote ON reply_vote.reply_id = reply.id
GROUP BY reply.id

我怎样才能做到这一点？

Answer 1

试试这个代码:

SELECT reply.id, reply.reply, 
    COUNT(reply_vote.id) as likes
    FROM reply 
    INNER JOIN reply_vote ON reply_vote.reply_id = reply.id 
    GROUP BY reply_vote.reply_id