(转)ORACLE WITH AS 用法

先举个例子吧:

有两张表,分别为A、B,求得一个字段的值先在表A中寻找,如果A表中存在数据,则输出A表的值;如果A表中不存在,则在B表中寻找,若B表中有相应记录,则输出B表的值;如果B表中也不存在,则输出"no records”字符串。

<wbr></wbr>

  1. with<wbr><wbr></wbr></wbr>
  2. sql1<wbr>as<wbr>(select<wbr>to_char(a)<wbr>s_name<wbr>from<wbr>test_tempa),<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  3. sql2<wbr>as<wbr>(select<wbr>to_char(b)<wbr>s_name<wbr>from<wbr>test_tempb<wbr>where<wbr>not<wbr>exists<wbr>(select<wbr>s_name<wbr>from<wbr>sql1<wbr>where<wbr>rownum=<span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">1</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">))<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  4. select<wbr>*<wbr>from<wbr>sql1<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
  5. union<wbr>all<wbr><wbr></wbr></wbr></wbr>
  6. select<wbr>*<wbr>from<wbr>sql2<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
  7. union<wbr>all<wbr><wbr></wbr></wbr></wbr>
  8. select<wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: blue; background-color: inherit;">'no<wbr>records'</wbr></span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr>from<wbr>dual<wbr><wbr></wbr></wbr></wbr></wbr></span></wbr>
  9. <wbr><wbr><wbr><wbr><wbr><wbr><wbr>where<wbr>not<wbr>exists<wbr>(select<wbr>s_name<wbr>from<wbr>sql1<wbr>where<wbr>rownum=<span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">1</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">)<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  10. <wbr><wbr><wbr><wbr><wbr><wbr><wbr>and<wbr>not<wbr>exists<wbr>(select<wbr>s_name<wbr>from<wbr>sql2<wbr>where<wbr>rownum=<span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">1</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">);<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

再举个简单的例子

with a as (select * from test)

select * from a;

其实就是把一大堆重复用到的SQL语句放在with as 里面,取一个别名,后面的查询就可以用它

这样对于大批量的SQL语句起到一个优化的作用,而且清楚明了

下面是搜索到的英文文档资料

About Oracle WITH clause<wbr><br> 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.</wbr>

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”:

<wbr><wbr> • The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.<br><wbr><wbr> • Formally, the “WITH clause” is called subquery factoring<br><wbr><wbr> • The SQL “WITH clause” is used when a subquery is executed multiple times<br><wbr><wbr> • Also useful for recursive queries (SQL-99, but not Oracle SQL)</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.<wbr><br> We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.</wbr>

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH<wbr><br> subquery_name<br> AS<br> (the aggregation SQL statement)<br> SELECT<br> (query naming subquery_name);</wbr>

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH<wbr> clause”:</wbr>

WITH
sum_sales AS<wbr><br><wbr> select <wbr><br><wbr><wbr><wbr> sum(quantity) all_sales from stores<br> number_stores AS<wbr><br><wbr> select <wbr><br><wbr><wbr><wbr> count(*) nbr_stores from stores<br> sales_by_store AS<br><wbr> select <wbr><br><wbr> store_name, sum(quantity) store_sales from<wbr><br><wbr> store natural join sales<br> SELECT<br><wbr><wbr> store_name<br> FROM<br><wbr><wbr> store,<br><wbr><wbr> sum_sales,<br><wbr><wbr> number_stores,<br><wbr><wbr> sales_by_store<br> where<br><wbr><wbr> store_sales &gt; (all_sales / nbr_stores)<br> ;</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH<wbr><br> subquery_name<br> AS<br> (the aggregation SQL statement)<br> SELECT<br> (query naming subquery_name);</wbr>


Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

=================================================================================

下面自己小试一把,当然,一点都不复杂,很简单很简单的例子,呵呵。

<wbr></wbr>

  1. SQL><wbr>create<wbr>table<wbr>t2(id<wbr></wbr></wbr></wbr></wbr>int);<wbr><wbr></wbr></wbr>
  2. <wbr><wbr></wbr></wbr>
  3. Table<wbr>created.<wbr><wbr></wbr></wbr></wbr>
  4. <wbr><wbr></wbr></wbr>
  5. SQL><wbr>create<wbr>table<wbr>t3(id<wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(0, 102, 153); background-color: inherit; font-weight: bold;">int</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">);<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr>
  6. <wbr><wbr></wbr></wbr>
  7. Table<wbr>created.<wbr><wbr></wbr></wbr></wbr>
  8. <wbr><wbr></wbr></wbr>
  9. SQL><wbr>insert<wbr>into<wbr>t2<wbr>values(<span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">1</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">);<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr>
  10. <wbr><wbr></wbr></wbr>
  11. 1<wbr>row<wbr>created.<wbr><wbr></wbr></wbr></wbr></wbr>
  12. <wbr><wbr></wbr></wbr>
  13. SQL><wbr>insert<wbr>into<wbr>t2<wbr>values(<span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">);<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr>
  14. <wbr><wbr></wbr></wbr>
  15. 1<wbr>row<wbr>created.<wbr><wbr></wbr></wbr></wbr></wbr>
  16. <wbr><wbr></wbr></wbr>
  17. SQL><wbr>insert<wbr>into<wbr>t3<wbr>values(<span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">);<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr>
  18. <wbr><wbr></wbr></wbr>
  19. 1<wbr>row<wbr>created.<wbr><wbr></wbr></wbr></wbr></wbr>
  20. <wbr><wbr></wbr></wbr>
  21. SQL><wbr>commit;<wbr><wbr></wbr></wbr></wbr>
  22. <wbr><wbr></wbr></wbr>
  23. Commit<wbr>complete.<wbr><wbr></wbr></wbr></wbr>
  24. <wbr><wbr></wbr></wbr>
  25. SQL><wbr>select<wbr>*<wbr>from<wbr>t2;<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  26. <wbr><wbr></wbr></wbr>
  27. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ID<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  28. ----------<wbr><wbr></wbr></wbr>
  29. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">1</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  30. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  31. <wbr><wbr></wbr></wbr>
  32. SQL><wbr>select<wbr>*<wbr>from<wbr>t3;<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  33. <wbr><wbr></wbr></wbr>
  34. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ID<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  35. ----------<wbr><wbr></wbr></wbr>
  36. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  37. SQL><wbr>with<wbr><wbr></wbr></wbr></wbr>
  38. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql1<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t2),<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  39. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql2<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t3)<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  40. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">4</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>t2<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  41. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">5</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>union<wbr><wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  42. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">6</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>t3;<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  43. sql2<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t3)<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  44. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>*<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  45. ERROR<wbr>at<wbr>line<wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">:<wbr><wbr></wbr></wbr></span></wbr></wbr></wbr>
  46. ORA-32035:<wbr>unreferenced<wbr>query<wbr>name<wbr>defined<wbr>in<wbr>WITH<wbr>clause<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  47. <wbr><wbr></wbr></wbr>
  48. --从这里可以看到,你定义了sql1和sql2,就得用它们哦,不然会报错的。<wbr><wbr></wbr></wbr>
  49. <wbr><wbr></wbr></wbr>
  50. SQL><wbr>with<wbr><wbr></wbr></wbr></wbr>
  51. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql1<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t2),<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  52. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql2<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t3)<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  53. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">4</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>sql1<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  54. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">5</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>union<wbr><wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  55. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">6</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>sql2;<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  56. <wbr><wbr></wbr></wbr>
  57. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ID<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  58. ----------<wbr><wbr></wbr></wbr>
  59. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">1</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  60. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  61. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  62. <wbr><wbr></wbr></wbr>
  63. --下面加个WHERE条件试试<wbr><wbr></wbr></wbr>
  64. <wbr><wbr></wbr></wbr>
  65. SQL><wbr>with<wbr><wbr></wbr></wbr></wbr>
  66. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql1<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t2),<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  67. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql2<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t3)<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  68. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">4</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>sql1<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  69. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">5</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>union<wbr><wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  70. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">6</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>sql2<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  71. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">7</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>where<wbr>id<wbr>in(</wbr></wbr></wbr></wbr></span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">,</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">);<wbr><wbr></wbr></wbr></span></wbr></wbr>
  72. <wbr><wbr></wbr></wbr>
  73. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ID<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  74. ----------<wbr><wbr></wbr></wbr>
  75. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">1</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  76. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  77. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  78. <wbr><wbr></wbr></wbr>
  79. --奇怪?为什么加了WHERE条件还是输出ID=1的记录了,继续往下看:<wbr><wbr></wbr></wbr>
  80. <wbr><wbr></wbr></wbr>
  81. SQL><wbr>with<wbr><wbr></wbr></wbr></wbr>
  82. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">2</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql1<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t2),<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  83. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>sql2<wbr>as<wbr>(select<wbr>*<wbr>from<wbr>t3)<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  84. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">4</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>sql1<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  85. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">5</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>where<wbr>id=</wbr></wbr></wbr></span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr>
  86. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">6</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>union<wbr><wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  87. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">7</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>select<wbr>*<wbr>from<wbr>sql2<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></wbr></wbr>
  88. <wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">8</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr>where<wbr>id=</wbr></wbr></wbr></span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;">;<wbr><wbr></wbr></wbr></span></wbr></wbr>
  89. <wbr><wbr></wbr></wbr>
  90. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ID<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  91. ----------<wbr><wbr></wbr></wbr>
  92. <wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: rgb(192, 0, 0); background-color: inherit;">3</span><span style="line-height: 18px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; color: black; background-color: inherit;"><wbr><wbr></wbr></wbr></span></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
  93. <wbr><wbr></wbr></wbr>
  94. --可以看到,每个条件是要针对每个SELECT语句的。<wbr><wbr></wbr></wbr>

好了就先记这些吧,以后看到了新的用法再补充。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值