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

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DrawingGridVerticalSpacing>7.8 磅</w:DrawingGridVerticalSpacing> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- /* Font Definitions */ @font-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-alt:SimSun; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} @font-face {font-family:"\@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1; mso-font-charset:134; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; mso-pagination:none; font-size:10.5pt; mso-bidi-font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:宋体; mso-font-kerning:1.0pt;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --><!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->

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