oracle 多个表的查询问题
现在有两个表：
dayorder：其中有字段custid，startdate。（这个表记录客户访问网站的历史表）
customer：字段有custid，custname（记录了客户的基本信息）
现在要统计在一个月当中访问次数多的前300名客户的资料，并显示每个客户的访问次数。
我的做法是：
select * from(select custid,count(*) vis_count from dayorder where startdate>='2005-09-01' and startdate<'2005-10-01' group by custid) order by vis_count desc
（如果把以上这句改为：
select * from(select custid,count(*) vis_count from dayorder where startdate>='2005-09-01' and startdate<'2005-10-01' group by custid) where rownum<=300 order by vis_count desc 则最大的两条记录没有显示在结果中）
之后就把前300条记录复制到一个临时表temp中，提取数据
select t.custid,t.vis_count,c.custname from temp t,customer c where t.custid=c.custid
请各位高手请教，如何根据上面的要求写一条更加高效的SQL语句？
[707 byte] By [
msdn] at [2007-10-25 21:51:04]
# 1 Re: oracle 多个表的查询问题
select a.custid, vis_count,custname from(select custid,count(*) vis_count from dayorder where startdate>='2005-09-01' and startdate<'2005-10-01' group by custid)a,customer b
where a.custid=b.custid
and a.rownum < 301
order by vis_count desc
# 5 Re: oracle 多个表的查询问题
select * from
(select max(c.custid), max(c.custname), count(d.custid) id
from customer c, dayorder d
where c.custid=d.custid and d.startdate>='2005-09-01' and d.startdate<'2005-10-01'
group by c.custid
order by id desc) a
where id<301
# 6 Re: oracle 多个表的查询问题
纠正
select * from
(select max(c.custid), max(c.custname), count(d.custid) id
from customer c, dayorder d
where c.custid=d.custid and d.startdate>='2005-09-01' and d.startdate<'2005-10-01'
group by c.custid
order by id desc) a
where rownum<301
