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

<p><!-- [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]-->
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">WITH </span>
<span style="">语句如下写法 其执行计划可以看的出，先执行一次查询，然后在进行二次统计。原来<span lang="EN-US">union all </span>
每个分句部分都要进行一次统计查询。<span lang="EN-US"> t_prouser_suc </span>
每周有<span lang="EN-US">2</span>
千万条数据
</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="">原来采用<span lang="EN-US">create table t_tmp </span>
临时表<span lang="EN-US"> </span>
又制造更多的物理<span lang="EN-US">IO</span>
</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="">使用<span lang="EN-US"> oracle</span>
临时表法 写法上很麻烦；
</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">with </span>
<span style="">就只能用于到一条语句中；不能用到同个过程多条语句里；
</span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">INSERT</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">INTO</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_buy_range_analyze</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">with</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_lotbuymone </span>
<span style="" lang="EN-US">as</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">SELECT</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotid,f_lotname,f_username,</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">sum</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(f_paymoney) F_BUYMONEY</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">FROM</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_prouser_suc t</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">WHERE</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_yearweek =</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">'201020'</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">GROUP</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">BY</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">F_LOTID,f_lotname,f_username</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
)</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">a.f_lotid,a.f_lotname,f_start,f_end,</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">count</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(f_username),</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">sum</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(f_buymoney)</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
(</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">* </span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_lotbuymone</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">union</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">all</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">-</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">10</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">as</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotid, </span>
<span style="" lang="EN-US">'</span>
<span style="" lang="EN-US"> </span>
<span style="">数</span>
<span style=""> </span>
<span style="" lang="EN-US">'</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">as</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotname,f_username,</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">sum</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(F_BUYMONEY) </span>
<span style="" lang="EN-US">as</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">F_BUYMONEY</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_lotbuymone </span>

</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">where</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotid </span>
<span style="" lang="EN-US">in</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">Distinct</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotid </span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_base_lotclass
</span>
<span style="" lang="EN-US">where</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_Lotbigid =</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">4</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">)</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">group</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">by</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_username</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">union</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">all</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">-</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">20</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">as</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotid, </span>
<span style="" lang="EN-US">'</span>
<span style="" lang="EN-US"> </span>
<span style="">竞</span>
<span style=""> </span>
<span style="" lang="EN-US">'</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">as</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotname,f_username,</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">sum</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(F_BUYMONEY) </span>
<span style="" lang="EN-US">as</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">F_BUYMONEY</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_lotbuymone </span>

</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">where</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotid </span>
<span style="" lang="EN-US">in</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">(</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">select</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">Distinct</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_lotid </span>
<span style="" lang="EN-US">from</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_base_lotclass
</span>
<span style="" lang="EN-US">where</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_Lotbigid =</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">3</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">)</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">group</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">by</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">f_username</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
) a</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">inner</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">join</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">t_prod_buyrang_config
b </span>
<span style="" lang="EN-US">on</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US"> a.f_lotid=b.f_lotid </span>
<span style="" lang="EN-US">and</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">a.f_buymoney
>= b.f_start </span>
<span style="" lang="EN-US">and</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">a.f_buymoney< b.f_end</span>
<span style="" lang="EN-US"> </span>
</p>
<p class="MsoNormal" style="text-align: left;" align="left"><span style="" lang="EN-US">
</span>
<span style="" lang="EN-US">group</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">by</span>
<span style="" lang="EN-US"> </span>
<span style="" lang="EN-US">a.f_lotid,a.f_lotname,f_start,f_end</span>
<span style="" lang="EN-US"> </span>
</p>