解释一下你B表各个字段的意义啊! statIP,statTime分别表示的是点击者的IP地址和点击时的时刻吗?如果statTime是点击时刻才可以到达你的目的,并且应该与A表无关了。 select newid,count(newid) as times from B where stattime<='2009-06-30' and stattime>='2009-06-01' group by newid order by times desc;
A表里存放:新闻记录( newid新闻记录ID, newtitle新闻标题, browseCount点击数(每点击一次本记录,这个值就+1)) B表里存放: (statid点击ID,newid新闻记录ID,statIP点击IP,statTime点击时间) 我想取出按newid的某段时间内(假如6月份)的点击数的总和倒序排列 browseCount是每条新闻的所有时间内的总点击数.我现在要按每条新闻的六月份的总点击倒序排列 我现在这么写了一条,但测试不合规则 : slect B.count(*) as B.num, A.* from B,A where B.newid=A.newid and B.statTime like '%2009-06%' order by B.num DESC
你不用group分组怎么行呢。另外as B.num估计也会出错,B表里面没有num字段 如果你一定要与A表对应的话,那用这个试试 select B.newid,count(B.newid) as click_num from B,A where B.newid=A.newid and stattime like'%2009-06' group by B.newid order by click_num desc;
感谢: daniel75122帅才 你的结果是正确的!!
select * from A where newid in (select newid from B where statTime like '%2009-06%' ) order by browseCount DESC 这样如何?
select * from A,B where A.newsid=B.newsid and statTime=6 GROUP BY newid order by browseCount desc
statIP,statTime分别表示的是点击者的IP地址和点击时的时刻吗?如果statTime是点击时刻才可以到达你的目的,并且应该与A表无关了。
select newid,count(newid) as times from B where stattime<='2009-06-30' and stattime>='2009-06-01' group by newid order by times desc;
A表里存放:新闻记录( newid新闻记录ID, newtitle新闻标题, browseCount点击数(每点击一次本记录,这个值就+1)) B表里存放: (statid点击ID,newid新闻记录ID,statIP点击IP,statTime点击时间) 我想取出按newid的某段时间内(假如6月份)的点击数的总和倒序排列 browseCount是每条新闻的所有时间内的总点击数.我现在要按每条新闻的六月份的总点击倒序排列 我现在这么写了一条,但测试不合规则 : slect B.count(*) as B.num, A.* from B,A where B.newid=A.newid and B.statTime like '%2009-06%' order by B.num DESC
如果你一定要与A表对应的话,那用这个试试
select B.newid,count(B.newid) as click_num from B,A where B.newid=A.newid and stattime like'%2009-06' group by B.newid order by click_num desc;
这样如何?