ORACLE WITH AS Ó÷¨

Ô­ÎÄ´«ËÍÃÅ£ºhttp://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx
¼ÇµÃÒÔÇ°ÔÚÂÛ̳Àï¿´µ½inthirtiesÓõ½¹ýWITH ASÕâ¸ö×ÖÑÛ£¬µ±Ê±Ã»ÌرðÔÚÒâ¡£½ñÌìÔÚÒ»¸öÌû×ÓÀïÓÖ¿´µ½ÓÐÈËÓÃÕâ¸ö£¬ËùÒÔ¾ÍÈ¥ÍøÉÏËÑÁËËÑÏà¹ØÄÚÈÝ£¬×Ô¼ºÐ¡ÊÔÁËÒ»°Ñ£¬Ð´ÏÂÀ´£¬·½±ãÒÔºóÍüÁ˵Ļ°Ñ§Ï°¡£
===================================================================================
ÏȾٸöÀý×Ó°É£º
ÓÐÁ½ÕÅ±í£¬·Ö±ðΪA¡¢B£¬ÇóµÃÒ»¸ö×ֶεÄÖµÏÈÔÚ±íAÖÐÑ°ÕÒ£¬Èç¹ûA±íÖдæÔÚÊý¾Ý£¬ÔòÊä³öA±íµÄÖµ£»Èç¹ûA±íÖв»´æÔÚ£¬ÔòÔÚB±íÖÐÑ°ÕÒ£¬ÈôB±íÖÐÓÐÏàÓ¦¼Ç¼£¬ÔòÊä³öB±íµÄÖµ£»Èç¹ûB±íÖÐÒ²²»´æÔÚ£¬ÔòÊä³ö"no records”×Ö·û´®¡£
with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);
Ôپٸö¼òµ¥µÄÀý×Ó
with a as (select * from test)
select * from a;
Æäʵ¾ÍÊÇ°ÑÒ»´ó¶ÑÖظ´Óõ½µÄSQLÓï¾ä·ÅÔÚwith as ÀïÃ棬ȡһ¸ö±ðÃû£¬ºóÃæµÄ²éѯ¾Í¿ÉÒÔÓÃËü
ÕâÑù¶ÔÓÚ´óÅúÁ¿µÄSQLÓï¾äÆðµ½Ò»¸öÓÅ»¯µÄ×÷Ó㬶øÇÒÇå³þÃ÷ÁË
ÏÂÃæÊÇËÑË÷µ½µÄÓ¢ÎÄÎĵµ×ÊÁÏ
About Oracle WITH clause
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.
The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:
   • The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
   • Formally, the “WITH clause” is called subquery factoring
   • The SQL “WITH clause” is used when a subquery is executed multiple times
   • Also useful for re