可以用一个表实现,加一个标记,'1'表示全部,‘0.5’表示部分有,'0'表示没有select sum(decode(avg(to_number(nvl(flag,'0'))),0,'0',1,'1','0.5')) as avgflag,phone
from order_info
where to_char(order_DATE,'yyyymmdd') between 20030301 and 20030331
group by phone
order by avg(to_number(nvl(flag,'0')));
from order_info
where to_char(order_DATE,'yyyymmdd') between 20030301 and 20030331
group by phone
order by avg(to_number(nvl(flag,'0')));
( select avg(to_number(nvl(flag,'0'))) as avgflag, phone
from order_info
where to_char(order_DATE,'yyyymmdd') between 20030301 and 20030331
group by phone
) b
where to_char(a.order_DATE,'yyyymmdd') between 20030301 and 20030331
and a.phone=b.phone
order by b.avgflag
曾听人说数据量大时用子查询的效率低于用中间表,是不是这样?
我在考虑作一个package,里边放一个procedure生成分类数据放到临时表,再放一个function查询订单等信息将结果返回,不知效率是否会高一些?
select avg(to_number(nvl(flag,'0'))) as avgflag,phone
from order_info
where to_char(order_DATE,'yyyymmdd') between 20030301 and 20030331
group by phone
order by avgflag
查到的记录比较多,大概2500条到5000条,用子查询好还是用临时表好呢?to beckhambobo:
快照怎样建,有何好处?