oraclewithas用法大全

《oraclewithas用法大全》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、ORACLEWITHAS用法有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出"norecords”字符串。viewplaincopytoclipboardprint?1.with  2.sql1 as (select to_char(a) s_name from test_tempa),  3.sql2 as (select to_char(b) s_name from tes

2、t_tempb where not exists (select s_name from sql1 where rownum=1))  4.select * from sql1  5.union all  6.select * from sql2  7.union all  8.select 'no records' from dual  9.       where not exists (select s_name from sql1 where rownum=1)  10.       and not exi

3、sts (select s_name from sql2 where rownum=1);  再举个简单的例子withaas(select*fromtest)select*froma;其实就是把一大堆重复用到的SQL语句放在withas里面，取一个别名，后面的查询就可以用它这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了下面是搜索到的英文文档资料AboutOracleWITHclauseStartinginOracle9irelease2weseeanincorporationoftheSQL-99“WI

4、THclause”,atoolformaterializingsubqueriestosaveOraclefromhavingtore-computethemmultipletimes.TheSQL“WITHclause”isverysimilartotheuseofGlobaltemporarytables(GTT),atechniquethatisoftenusedtoimprovequeryspeedforcomplexsubqueries.HerearesomeimportantnotesabouttheO

5、racle“WITHclause”:  •TheSQL“WITHclause”onlyworksonOracle9irelease2andbeyond.  •Formally,the“WITHclause”iscalledsubqueryfactoring  •TheSQL“WITHclause”isusedwhenasubqueryisexecutedmultipletimes  •Alsousefulforrecursivequeries(SQL-99,butnotOracleSQL)Tokeepitsimpl

6、e,thefollowingexampleonlyreferencestheaggregationsonce,wheretheSQL“WITHclause”isnormallyusedwhenanaggregationisreferencedmultipletimesinaquery.WecanalsousetheSQL-99“WITHclause”insteadoftemporarytables.TheOracleSQL“WITHclause”willcomputetheaggregationonce,givei

7、taname,andallowustoreferenceit(maybemultipletimes),laterinthequery.TheSQL-99“WITHclause”isveryconfusingatfirstbecausetheSQLstatementdoesnotbeginwiththewordSELECT.Instead,weusethe“WITHclause”tostartourSQLquery,definingtheaggregations,whichcanthenbenamedinthemai

8、nqueryasiftheywere“real”tables:WITHsubquery_nameAS(theaggregationSQLstatement)SELECT(querynamingsubquery_name);Retuningtoouroversimplifiedexample,let’sreplacethetemporarytableswith