oracle 集合表,ORACLE SQL总结一：集合函数和多表查询

ORACLE SQL总结一：集合函数和多表查询

1、集合函数总结

1.1 where从句中不能使用集合函数，如果需要在判断语句中使用集合函数，使用having从句。

1.2 集合函数，除COUNT,GROUPING,GROUP,其余的函数在计算时都忽略NULL。

1.3 在select从句中没有使用集合函数的列，就必须出现在group by从句中。即一个列要么在select从句中使用集合函数，要么放在group by从句中，包括在having中出现的列。

1.4 where从句和having从句可以在SQL语句中一起使用，作用不同。where从句作用与group之前的条件判断，having从句作用与group之后的条件判断。

1.5 Grouping 函数使用小结，很有趣，已发知识库。

对oracle官方手册上的解释翻译

Purpose  www.2cto.com

GROUPING distinguishes superaggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP and CUBE produce superaggregate rows where the set of all values is represented by null. Using the GROUPING function, you can distinguish a null representing the set of all values in a superaggregate row from a null in a regular row.

The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The data type of the value returned by the GROUPING function is Oracle NUMBER. Refer to the SELECT group_by_clause for a discussion of these terms.

翻译如下：

GROUPING用来从分组后的regular行中区别出superaggregate行，regular行、superaggregate行是由GROUP BY的扩展函数如ROLLUP、CUBE产生的，superaggregate行的特点是所有(列)值为null，regular行的特点是有一个(列)值为null，使用GROUPING函数可以从regular行中区别出superaggregate行。、

www.2cto.com

作为GROUPING函数中的表达式必须是GROUP BY从句中的一项，如果该表达式值对应的行中所有的列值为空，则GROUPING函数返回1，否则返回0.GROUPING函数返回的数据类型为NUMBER型。

2、多表查询

2.1 select * from table1,table2 应该使用的是inner join查询

2.2 select t1.column1,t1.* from table1 t1 join table2 t2 using(column1)   错误

使用using()有几个条件：一是table1和table2都有该列，并且列名相同；二是select从句中不能使用tablename.column或tablenamealias.column这种用法,所有的join从句都有此项限制。

2.3 NATURAL JOIN

摘自oralce sql references 中 select语法解释部分：

....................

The NATURAL keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. If two columns with the same name do not have compatible data types, then an error is raised. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

On occasion, the table pairings in natural or cross joins may be ambiguous. For example, consider the following join syntax:

www.2cto.com

a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1

This example can be interpreted in either of the following ways:

a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1)

(a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1

To avoid this ambiguity, you can use parentheses to specify the pairings of joined tables. In the absence of such parentheses, the database uses left associativity, pairing the tables from left to right.

翻译如下

NATURAL关键字使得执行natural join,它是基于两个表中所有相同列名的列，它从两个表中查询行，这些表的列(列名相同)有相同的值。

如果两个列(分别在不同的表中)有相同的列名但是数据类型不兼容，使用natural join时会发生错误。

当在natural join使用某列联接时，该列(在select从句)不能使用表名.列名或表名的同义词.列名这种命名方式。————所有的join都有此项要求。

但是，有时使用natural或cross join进行表之间匹配比较难理解，比如如下的例子：

a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1

www.2cto.com

这个例子可以解释为下面两种方式

解释一：a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1)

解释二：(a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1

为了避免解释上的模糊不清，你可以使用圆括号()来定义匹配的表，在缺少圆括号时，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.

Evaluates 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.

Evaluates 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