select a.username,sum(a.count+b.count) as total_count from 帖子表 a, 回复表 b where a.username=b.username group by a.username order by total_count desc limit 10;
select t.username,sum(t.total_count) as all_count ( select a.username,sum(ifnull(a.`count`,0)+ifnull(b.`count`,0)) as total_count from 帖子表 a, 回复表 b where a.username=b.username group by a.username union all select a.username,sum(ifnull(a.`count`,0)) from 帖子表 a left join 回复表 b on a.username=b.username where b.username is null group by a.username union all select b.username,sum(ifnull(b.`count`,0)) from 帖子表 a right join 回复表 b on a.username=b.username where a.username is null group by b.username ) t group by t.username order by all_count desc limimt 10
建议你可以直接下个DZ的BBS论坛看一下它的设计。一般没有这个count字段直接根据 username 来统计发贴量和回复量帖子表(帖子id,username ,ptime, ....) 回复表(回复ID,帖子id,username , ptime ,.....)然后就直接根据这两个表 select username ,count(*) from 帖子表 where ptime>date_sub(now(),interval 1 day) group by username order by 2 desc;
SQL codeselect t.username,sum(t.total_count) as all_count ( select a.username,sum(ifnull(a.`count`,0)+ifnull(b.`count`,0)) as total_count from 帖子表 a, 回复表 b where a.username=b.username group by a.username union all select a.username,sum(ifnull(a.`count`,0)) from 帖子表 a left join 回复表 b on a.username=b.username where b.username is null group by a.username union all select b.username,sum(ifnull(b.`count`,0)) from 帖子表 a right join 回复表 b on a.username=b.username where a.username is null group by b.username ) t where a.date`=current_date() group by t.username order by all_count desc limit 10
我实验了你的sql,产生了这个错误You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( select a.username,sum(ifnull(a.`username`,0)+ifnull(b.`username`,0)) as total' at line 2
前面漏了个from select t.username,sum(t.total_count) as all_count from ( ...
vinsonshen 的sql 统计出来的 all_count都是 0 , 我的数据库是这样的:帖子的: 回复的: article commentsid id username username times times是通过count(username) 来统计的 也只取 times=当天的记录
贴记录出来吧,帖子、回复数量一起相加?通过id还是username连接?
select username,count(*) from ( select username from article where times=curdate() union all select username from comments where times=curdate() ) t group by username order by 2 desc
或者select username,sum(cnt) from ( select username,count(*) as cnt from article where times=curdate() group by username union all select username,count(*) as cnt from comments where times=curdate() group by username ) t group by username order by 2 desc两种方法速度上略有差异。
try: select username,count(*) from ( select id,username from article union all select id,username from comments) where times=curdate() group by username order by count(*) desc limit 10
select top 10 * select username,sum(count) count from ( select username,count from 帖子表 union all select username,count from 回复表 ) as tab group by username order by count desc
问题解决 ACMAIN_CHM 的 是正确的多谢各位高手最终的sqlselect username,sum(cnt) from ( select username,count(*) as cnt from article where DATE_FORMAT(madetimes,'%Y-%m-%d')=curdate() group by username union all select username,count(*) as cnt from comments where DATE_FORMAT(madetimes,'%Y-%m-%d')=curdate() group by username ) t group by username order by 2 desc limit 10
如果你的每天发贴量比较大,则用下面的语句就行了。select username,sum(cnt) from ( select username,count(*) as cnt from article where times=curdate() group by username union all select username,count(*) as cnt from comments where times=curdate() group by username ) t group by username order by 2 desc limit 10;
统计数量相加,而不同的也列出来,有可能发贴的没回复,回复的没发贴,
(
select a.username,sum(ifnull(a.`count`,0)+ifnull(b.`count`,0)) as total_count from 帖子表 a, 回复表 b where a.username=b.username group by a.username
union all
select a.username,sum(ifnull(a.`count`,0)) from 帖子表 a left join 回复表 b on a.username=b.username where b.username is null group by a.username
union all
select b.username,sum(ifnull(b.`count`,0)) from 帖子表 a right join 回复表 b on a.username=b.username where a.username is null group by b.username
) t
group by t.username
order by all_count desc
limimt 10
回复表(回复ID,帖子id,username , ptime ,.....)然后就直接根据这两个表 select username ,count(*) from 帖子表 where ptime>date_sub(now(),interval 1 day) group by username order by 2 desc;
-------------------------------
在6楼的基础上对2个表加个日期判断就可以了 `date`=current_date()
(
select a.username,sum(ifnull(a.`count`,0)+ifnull(b.`count`,0)) as total_count from 帖子表 a, 回复表 b where a.username=b.username group by a.username
union all
select a.username,sum(ifnull(a.`count`,0)) from 帖子表 a left join 回复表 b on a.username=b.username where b.username is null group by a.username
union all
select b.username,sum(ifnull(b.`count`,0)) from 帖子表 a right join 回复表 b on a.username=b.username where a.username is null group by b.username
) t
where a.date`=current_date()
group by t.username
order by all_count desc
limit 10
我的表中也没有count 字段
需要 count(username) 才行,
我实验了你的sql,产生了这个错误You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(
select a.username,sum(ifnull(a.`username`,0)+ifnull(b.`username`,0)) as total' at line 2
select t.username,sum(t.total_count) as all_count
from
(
...
我的数据库是这样的:帖子的: 回复的:
article commentsid id
username username
times times是通过count(username) 来统计的
也只取 times=当天的记录
(
select username from article where times=curdate()
union all
select username from comments where times=curdate()
) t
group by username
order by 2 desc
(
select username,count(*) as cnt from article where times=curdate() group by username
union all
select username,count(*) as cnt from comments where times=curdate() group by username
) t
group by username
order by 2 desc两种方法速度上略有差异。
select username,count(*) from (
select id,username from article
union all
select id,username from comments)
where times=curdate() group by username
order by count(*) desc limit 10
xxxx1 aaaa 2009-10-20 0:0:12
xxxx3 cccc 2009-10-20 0:0:12
xxxx4 dddd 2009-10-19 0:0:12 comments id username times
xxxx1 aaaa 2009-10-20 0:0:12
xxxx2 bbbb 2009-10-20 0:0:12
xxxx4 dddd 2009-10-19 0:0:12假设当天是 2009-10-20 那么 aaaa 的帖子数是 他在article 和 comments 中的相加 为2
bbbb 和 cccc 只在一个表中有记录 所以为 1
dddd 的记录在范围之外 不取
select username,sum(count) count from
(
select username,count from 帖子表
union all
select username,count from 回复表
) as tab
group by username
order by count desc
(
select username,count(*) as cnt from article where DATE_FORMAT(madetimes,'%Y-%m-%d')=curdate() group by username
union all
select username,count(*) as cnt from comments where DATE_FORMAT(madetimes,'%Y-%m-%d')=curdate() group by username
) t
group by username
order by 2 desc
limit 10
如果你的每天发贴量比较大,则用下面的语句就行了。select username,sum(cnt) from
(
select username,count(*) as cnt from article where times=curdate() group by username
union all
select username,count(*) as cnt from comments where times=curdate() group by username
) t
group by username
order by 2 desc
limit 10;
不同的应用。UNION是取不重复的, 而UNION ALL则是不做过滤,自然会快一些。