MYsql查询故障排除

Question

SELECT contest.contest_id 
FROM db_user
LEFT JOIN participants ON participants.user_id = db_user.db_user
LEFT JOIN contest ON participants.contest_id = contest.contest_id
WHERE contest.user_id=$user
group by participants.contest_id,participants.user_id";

这将返回参加所有竞赛的所有用户，并且运行良好，但我需要找出它返回的行数。对于上面返回的5行，我希望结果为

Contest_Id       Number of users
1                       3
2                       4

Answer 1

您需要将COUNT聚合函数与适当的HAVING子句一起使用。请参阅以下内容：HAVING Clause

Answer 2

试试这个：

 select contest.contest_id ,Count(*) as 'No of Users'
    FROM db_user
    LEFT JOIN participants ON participants.user_id = db_user.db_user
    LEFT JOIN contest ON participants.contest_id = contest.contest_id
    WHERE contest.user_id=$user
    group by participants.contest_id;

Answer 3

尝试：

    SELECT contest.contest_id, 
(select count(*) from db_user 
      inner JOIN participants ON participants.user_id = db_user.user_id and                   
      participants.contest_id = contest.contest_id)
    FROM contest