select c.sname,count(b.rid)
from t_sinfo c,t_pellib b,t_fullib a,
where a.tid=b.rid and a.sid=c.sid
and b.dtime>=.....
and b.dtime<=.....
and b.rtype=1
group by(c.sname)
order by count(b.rid) desc;
这句SQL语句测试了,结果是正确的!
但是现在想去前十条数据,加上 and rownum<=10 后!结果只有7条,而且得到的sname,count(b.rid)还不对应!求解释,求解决方案!
(select c.sname,count(b.rid),rownum rn
from t_sinfo c,t_pellib b,t_fullib a,
where a.tid=b.rid and a.sid=c.sid
and b.dtime>=.....
and b.dtime<=.....
and b.rtype=1
group by(c.sname)
order by count(b.rid) desc)
where
rn < 10
from t_sinfo c,t_pellib b,t_fullib a,
where a.tid=b.rid and a.sid=c.sid
and b.dtime>=.....
and b.dtime<=.....
and b.rtype=1)里选出的前十行
肯定是的先排序再加行号
select sname,rid
from (select sname,rid,rownum rn
from (select c.sname,count(b.rid) rid
from t_sinfo c,t_pellib b,t_fullib a,
where a.tid=b.rid and a.sid=c.sid
and b.dtime>=.....
and b.dtime<=.....
and b.rtype=1
group by(c.sname)
order by count(b.rid) desc) k) where rn<=10select sname,rid
from (select c.sname,count(b.rid) rid
from t_sinfo c,t_pellib b,t_fullib a,
where a.tid=b.rid and a.sid=c.sid
and b.dtime>=.....
and b.dtime<=.....
and b.rtype=1
group by(c.sname)
order by count(b.rid) desc) where rownum<11