请教一个多表count()的问题。

[zxywd]

各位下午好，小弟最近在做自己的毕业论文，遇到了一个棘手的问题。就是通过多表count()来统计某个版块中帖子总数和回帖总数以及版主。
有四个表：user表，board表，post表，reply表

select count(post.post_id) from post where post.board_id=1
得到如下结果：
count(post.post_id)
3

select 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呢？
怎么解决这个问题？谢谢各位。

[wwwwb]

这种 分组，一般每个表分组，再连接

[ACMAIN_CHM]

为什么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

[ACMAIN_CHM]

SELECT board.name, count( distinct post.post_id ) , count(distinct 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

[zxywd]

楼上的兄弟：怎么分组，是union吗？

[zxywd]

多谢了。

[WWWWA]

SELECT board.name, count( a.post_id ) , count( c.reply_id ) , d.username
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