再说WITH子句

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1670

 

Computation Using the WITH Clause

 

   The WITH clause (formally known assubquery_factoring_clause) enables you to reuse thesame query block in aSELECT statement when it occursmore than once within a complex query. --WITH子句让你能够在一个复杂SELECT查询中重用相同的查询块(利用名字)

 

WITH is a part of theSQL-99 standard. This is particularly useful when a query hasmultiple references to the same query block and there are joins andaggregations.

 

 Using theWITH clause, Oracle retrieves the results of a queryblock and stores them in the user's temporarytablespace.--使用WITH子句，甲骨文查询一个查询块的结果并且把他们存储在用户临时表空间上

 

 Note that OracleDatabase does not support recursive use of theWITHclause.

--注意甲骨文数据库并不支持循环使用WITH子句。

 

   The following query is an example ofwhere you can improve performance and write SQL more simply byusing theWITHclause.---下边的查询的例子，你能够改善性能和书写SQL简单。

 

 The query calculates the sum of sales for eachchannel and holds it under the namechannel_summary.Then it checks each channel's sales total to see if any channel'ssales are greater than one third of the total sales. By using theWITH clause, thechannel_summary data iscalculated just once, avoiding an extra scan through the largesales table.

Example 20-15 WITHClause

WITH channel_summary AS (SELECT channels.channel_desc, SUM(amount_sold)AS channel_total FROM sales, channelsWHERE sales.channel_id = channels.channel_id GROUP BY channels.channel_desc)SELECT channel_desc, channel_totalFROM channel_summary WHERE channel_total > (SELECT SUM(channel_total) * 1/3FROM channel_summary);CHANNEL_DESC         CHANNEL_TOTAL-------------------- -------------Direct Sales            57875260.6

Note that this example could also be performed efficiently usingthe reporting aggregate functions described inChapter 21, "SQL for Analysis and Reporting".