WITH TABLENAME AS () 类似于临时表用法提高性能

WITH 语句如下写法 其执行计划可以看的出，先执行一次查询，然后在进行二次统计。原来union all 每个分句部分都要进行一次统计查询。 t_prouser_suc 每周有2 千万条数据

原来采用create table t_tmp  临时表  又制造更多的物理IO

使用 oracle 临时表法 写法上很麻烦；

with 就只能用于到一条语句中；不能用到同个过程多条语句里；

INSERT INTO t_buy_range_analyze

          with t_lotbuymone as (

                SELECT f_lotid,f_lotname,f_username, sum (f_paymoney) F_BUYMONEY

                FROM t_prouser_suc t

                WHERE f_yearweek = '201020'

                GROUP BY F_LOTID,f_lotname,f_username

          )

          select a.f_lotid,a.f_lotname,f_start,f_end, count (f_username), sum (f_buymoney)

          from

          (

          select * from t_lotbuymone

          union all

          select - 10 as f_lotid, ' 数 ' as f_lotname,f_username, sum (F_BUYMONEY) as F_BUYMONEY

           from t_lotbuymone

           where f_lotid in ( select Distinct f_lotid from t_base_lotclass where f_Lotbigid = 4 )

           group by f_username

          union all

          select - 20 as f_lotid, ' 竞 ' as f_lotname,f_username, sum (F_BUYMONEY) as F_BUYMONEY

           from t_lotbuymone

           where f_lotid in ( select Distinct f_lotid from t_base_lotclass where f_Lotbigid = 3 )

           group by f_username

          ) a

          inner join t_prod_buyrang_config  b on   a.f_lotid=b.f_lotid and a.f_buymoney >= b.f_start and a.f_buymoney< b.f_end

          group by a.f_lotid,a.f_lotname,f_start,f_end