现在有两个表: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语句?
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语句?
where a.custid=b.custid
and a.rownum < 301
order by vis_count desc
SELECT a.custid, vis_count, custname
FROM (SELECT ROWNUM AS ID, custid, vis_count
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.ID < 301
ORDER BY vis_count DESC
(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
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