ORACLE SQL总结一：集合函数和多表查询(二)
了避免解释上的模糊不清，你可以使用圆括号()来定义匹配的表，在缺少圆括号时，oracle从左自右解析表达式，比如上面的例子应该解释二。
2.4 当两个表使用join从句联接，并且两个表有相同的列名，在select 从句中要指明表名，例子如下
select order_id,product_id,unit_price*quantity "total" from order_items oi join orders o on(o.order_id=oi.order_id) where o.order_date>sysdate-7
*
ERROR at line 1:
ORA-00918: column ambiguously defined
2.5 下面这条语句为什么可执行？and怎么能放在那里？
答：是condition语句，将多个条件用and组合，OR等其他操作符应该也可以。
SQL> select i.product_id,i.quantity_on_hand,pi.supplier_id from product_information pi join inventories i on(pi.product_id=i.product_id) andquantity_on_hand<5;
2.6 注意oracle官方手册中each 与 every 的不同
ANY/SOME
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values.
eva luates to FALSE if the query returns no rows.
each 每个，只有一个满足要求就可以
www.2cto.com
ALL
Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values.
eva luates to TRUE if the query returns no rows.
every 每个都要满足要求
2.7 下面这句话是什么意思？
If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.
如果子查询返回0行，则子查询表达式返回的值是NULL
2.8 WITH从句
当查询中多次用到某一部分时，可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中，避免了重复解析，所以执行效率会提高不少。临时表在一次查询结束自动清除。
一般语法格式：
with
alias_name1 as (subquery1),
alias_name2 as (subQuery2),
……
alias_nameN as (subQueryN)
select col1,col2…… col3
from alias_name1,alias_name2……,alias_nameN
Oracle with语句的例子：
www.2cto.com
SQL> WITH
Q1 AS (SELECT 3 + 5 S FROM DUAL),
Q2 AS (SELECT 3 * 5 M FROM DUAL),
Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
SELECT * FROM Q3;
输出结果：
S M S+M S*M
---------- ---------- ---------- ----------
8 15 23 120
2.9 下面这句话是什么意思？翻译正确吗？
A subquery is called a single-row subquery when The inner query returns a single value to the main query
当内部查询返回单个值给主查询时，子查询被称为单行查询。
作者 yeyelei