oracle with 语句语法及示例

《oracle with 语句语法及示例》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、OracleWith语句语法及示例1、一个完整的OracleWith语句实例：insertintosms_tmp_stop_circlenum_zsdx–将下面查询的结果插入到临时表中WITHselectdata--OracleWith开始的查询语句AS(SELECTTRUNC(createtime)senddate,agentid,srcnum,ROUND(SUM(CASEWHENresult='4'THEN1ELSE0END)/COUNT(*)*100,2)ASratioFROMZSDX_SMS_OTHERSEND_DETAILSWHER

2、EagentidLIKE'zsdx%'ANDcreatetime>=TO_DATE('2010-12-3000:00:00','yyyy-mm-ddhh24:mi:ss')ANDcreatetime

3、

4、

5、'%'AS当天状态返回失败比率,SYSDATEAS运维停用时间,'李兴宗'AS停用操作FROM(SELECTagentid,srcnum,passwd,ACCOUNT,createtimeFROMZSDX_SMS_CIRCLENUMWHEREsrcnumIN(SELECTsrcnumFROMselectdataWHEREratio>=100))a1,(SELECTsrcnum,ratio,senddateFROMselectdataWHEREratio>=100)a2WHEREa1.srcnum=a2.srcnum2、详解：Startin

6、ginOracle9irelease2weseeanincorporationoftheSQL-99"WITHclause",atoolformaterializingsubqueriestosaveOraclefromhavingtore-computethemmultipletimes.TheSQL"WITHclause"isverysimilartotheuseofGlobaltemporarytables(GTT),atechniquethatisoftenusedtoimprovequeryspeedforcomplexsubque

7、ries.HerearesomeimportantnotesabouttheOracle"WITHclause":?TheSQL"WITHclause"onlyworksonOracle9irelease2andbeyond.?Formally,the"WITHclause"iscalledsubqueryfactoring?TheSQL"WITHclause"isusedwhenasubqueryisexecutedmultipletimes?Alsousefulforrecursivequeries(SQL-99,butnotOracle

8、SQL)Tokeepitsimple,thefollowingexampleonlyreferencestheaggregationsonce,wheretheSQL"WITHclause"isnormallyusedwhenanaggregationisreferencedmultipletimesinaquery.WecanalsousetheSQL-99"WITHclause"insteadoftemporarytables.TheOracleSQL"WITHclause"willcomputetheaggregationonce,gi

9、veitaname,andallowustoreferenceit(maybemultipletimes),laterinthequery.TheSQL-99"WITHclause"isveryconfusingatfirstbecausetheSQLstatementdoesnotbeginwiththewordSELECT.Instead,weusethe"WITHclause"tostartourSQLquery,definingtheaggregations,whichcanthenbenamedinthemainqueryasift

10、heywere"real"tables:WITHsubquery_nameAS(theaggregationSQLstatement)SELECT(querynam