各位下午好,小弟最近在做自己的毕业论文,遇到了一个棘手的问题。就是通过多表count()来统计某个版块中帖子总数和回帖总数以及版主。
有四个表:user表,board表,post表,reply表select count(post.post_id) from post where post.board_id=1
得到如下结果:
count(post.post_id)
3select count(reply.reply_id) from reply where reply.board_id=1
得到如下结果:
count(reply.reply_id)
6但是:
SELECT board.name, count( post.post_id ) , count( reply.reply_id ) , user.username
FROM board
INNER JOIN post ON board.board_id = post.board_id
INNER JOIN reply ON board.board_id = reply.board_id
LEFT JOIN user ON board.admin_id = user.user_id
WHERE board.board_id =1
确得到如下结果:
name count( post.post_id ) count( reply.reply_id ) username
PHP技术专区 18 18 zxywd为什么count(post.post_id)和count(reply.reply_id)都是18呢?
怎么解决这个问题?谢谢各位。
有四个表:user表,board表,post表,reply表select count(post.post_id) from post where post.board_id=1
得到如下结果:
count(post.post_id)
3select count(reply.reply_id) from reply where reply.board_id=1
得到如下结果:
count(reply.reply_id)
6但是:
SELECT board.name, count( post.post_id ) , count( reply.reply_id ) , user.username
FROM board
INNER JOIN post ON board.board_id = post.board_id
INNER JOIN reply ON board.board_id = reply.board_id
LEFT JOIN user ON board.admin_id = user.user_id
WHERE board.board_id =1
确得到如下结果:
name count( post.post_id ) count( reply.reply_id ) username
PHP技术专区 18 18 zxywd为什么count(post.post_id)和count(reply.reply_id)都是18呢?
怎么解决这个问题?谢谢各位。
MYSQL,或者说所有的数据库都是先做JOIN查询,然后根据JOIN查询后的结果再进行COUNT。 你可以自己写一下JOIN后的结果是什么样。
FROM board
INNER JOIN post ON board.board_id = post.board_id
INNER JOIN reply ON board.board_id = reply.board_id
LEFT JOIN user ON board.admin_id = user.user_id
WHERE board.board_id =1
FROM board
INNER JOIN post ON board.board_id = post.board_id
INNER JOIN reply ON board.board_id = reply.board_id
LEFT JOIN user ON board.admin_id = user.user_id
WHERE board.board_id =1
FROM board
INNER JOIN (select board_id from post group by board_id) a ON board.board_id = a.board_id
INNER JOIN (select board_id from reply group by board_id) c ON board.board_id = c.board_id
LEFT JOIN (selecy user_id from user group by user_id) d ON board.admin_id = d.user_id
WHERE board.board_id =1