---
title: "WITH TABLENAME AS () 类似于临时表用法提高性能"
author: "winzenghua"
date: "2010-05-21T17:07:00Z"
tags: []
slug: "with-tablename-as"
draft: false
lastmod: "2010-05-21T17:07:00Z"
id: 987763
url: "https://java4gis.iteye.com/blog/987763"
---

&lt;!-- \[if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt; &lt;w:View&gt;Normal&lt;/w:View&gt; &lt;w:Zoom&gt;0&lt;/w:Zoom&gt; &lt;w:PunctuationKerning/&gt; &lt;w:DrawingGridVerticalSpacing&gt;7.8 磅&lt;/w:DrawingGridVerticalSpacing&gt; &lt;w:DisplayHorizontalDrawingGridEvery&gt;0&lt;/w:DisplayHorizontalDrawingGridEvery&gt; &lt;w:DisplayVerticalDrawingGridEvery&gt;2&lt;/w:DisplayVerticalDrawingGridEvery&gt; &lt;w:ValidateAgainstSchemas/&gt; &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt; &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt; &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt; &lt;w:Compatibility&gt; &lt;w:SpaceForUL/&gt; &lt;w:BalanceSingleByteDoubleByteWidth/&gt; &lt;w:DoNotLeaveBackslashAlone/&gt; &lt;w:ULTrailSpace/&gt; &lt;w:DoNotExpandShiftReturn/&gt; &lt;w:AdjustLineHeightInTable/&gt; &lt;w:BreakWrappedTables/&gt; &lt;w:SnapToGridInCell/&gt; &lt;w:WrapTextWithPunct/&gt; &lt;w:UseAsianBreakRules/&gt; &lt;w:DontGrowAutofit/&gt; &lt;w:UseFELayout/&gt; &lt;/w:Compatibility&gt; &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;!\[endif]--&gt;&lt;!-- \[if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState="false" LatentStyleCount="156"&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;!\[endif]--&gt;&lt;!-- /* 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;} --&gt;&lt;!-- \[if gte mso 10]&gt; &lt;mce:style&gt;&lt;!-- /* 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;} --&gt; &lt;!-- \[endif]--&gt;

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 &gt;= b.f\_start and a.f\_buymoney&lt; b.f\_end

group by a.f\_lotid,a.f\_lotname,f\_start,f\_end