第3,6章节

3.3对行进行分组
3.3.1使用GROUP  BY子句对行进行分组
GROUP  BY子句可以用于将行分组为具有相同列值的多个部分
2．行分组可以使用聚合函数。聚合函数对每组中的行进行计算，并为每组行都返回一个结果。
3．默认情况下，GROUP  BY子句会根据分组列的值按照升序次序对行进行排序。ORDER BY 子句用于指定对哪一列进行排序。
在SELECT子句中不一定要包含GROUP  BY子句所使用的列。
3.3.2调用聚合函数的错误用法
如果查询中包含聚合函数，而所选择的列并不在聚合函数中，那么这些列就必须在GROUP BY子句中。
还有，不能在WHERE子句中使用聚合函数来限制行。如果试图这样做，就会出现下面的错误：ORA-00934：group  function  is  not  allowed  here.
这个错误之所以会出现是因为WHERE子句只能用来对单行而不是分组进行过滤。要过滤分组行，可以使用HAVING子句。

3.3.3使用HAVING子句过滤行分组
SELECT …
FROM…
WHERE…
GROUP BY…
HAVING…
ORDER BY…;
但是HAVING必须与GROUP  BY 子句一起使用。
3.3.4组合使用WHERE和GROUP BY子句
这样使用时，WHERE子句首先对返回行进行过滤，然后GROUP BY子句对保留的行进行分组。
3.3.5组合使用WHERE、 GROUP  BY和HAVING子句
这样使用时，WHERE子句首先对返回行进行过滤，然后GROUP BY子句对保留的行进行分组，最后HAVING子句对行分组进行过滤，最后ORDER BY再指定对哪一列进行排序。

6子查询
6.1子查询的类型
6.2编写单行子查询
单行子查询不向外部的SQL语句返回结果，或者只返回一行。子查询可以放到SELECT语句的WHERE子句、HAVING子句或FROM子句中。
SELECT  FIRST_NAME,LASST_NAME
FROM  COUSTOMERS
WHERE  CUSTOMER_ID =
(SELECT  CUSTOMER_ID
FROM CUSTOMERS
WHERE  LAST_NAME=’Brown’);

6.2.1在WHERE子句中使用子查询
SELECT  FIRST_NAME,LASST_NAME
FROM  COUSTOMERS
WHERE  CUSTOMER_ID =
(SELECT  CUSTOMER_ID
FROM CUSTOMERS
WHERE  LAST_NAME=’Brown’);
6.2.2在HAVING子句中使用子查询
这个例子检索那些平均价格低于同类产品的平均价格最大值的产品的product_type_id和平均价格：
SELECT product_type_id,AVG(price)
FROM product
GROUP BY products_type_id
HAVING AVG(price)<
(SELECT MAX(AVG(price))
FROM products
GROUP BY product_type_id);
6.2.3在From子句中使用子查询(内联视图)
这个例子在外部查询中从products表中检索product_id和price列，在子查询中检索一种产品已经被购买的次数：
SELECT  prds.product_id,price,purchases_data.product_count
FROM  products prds,
(SELECT product_id,COUNT(product_id)  product_count
FROM  purchases
GROUP  BY product_id)  purchases_data
WHERE  prds.product_id=purchases_data.product_id;
子查询从purchases表中检索出prodcut_id和COUNT(product_id),并将其返回给外部的查询。可以看到，子查询的输出结果正好是外部查询的FROM子句的另外一个数据源。
子查询不能包含ORDER  BY子句，必须在外部查询中进行任何排序。
6.3编写多行子查询
多行子查询可以向外部的SQL语句返回一行或多行记录。要处理返回多行记录的子查询，外部查询可以使用IN，ANY或ALL操作符。
6.4编写多列子查询
实际上并没有限定子查询只能返回一列：我们可以编写返回多列的子查询。下面这个例子检索每种产品类型中价格最低的产品:
SELECT  product_id,product_type_id,name,price
FROM  products
WHERE  (product_type_id,price)  IN
(SELECT product_type_id,MIN(price)
FROM  products
GROUP BY product_type_id);
子查询返回了两列：product_type_id和price列的最小值；而外部查询的WHERE子句中的圆括号中就包含了两列：product_type_id和price。


6.5编写关联子查询
关联子查询会引用外部查询中的一列或多列。这种子查询之说以被称为是关联子查询，是因为子查询的确与外部查询有关。当问题的答案需要依赖于外部查询中包含的每一行中的值时，
通常就需要使用关联子查询。
下面这个例子检索那些价格高于同类产品的平均价格的产品：
SELECT  product_id, product_type_id,name,price
FROM  products  outer
WHERE   price>
(SELECT  AVG(price)
FROM  products  inner
WHERE  inner.product_type_id=outer.product_type_id);
在关联子查询中，外部查询中的每一行都被一次一行地传递给子查询。子查询依次读取外部查询中的每一行的值，并将其应用到子查询上，直到外部查询中的所有行都被处理完为止。
然后返回整个查询的结果。
EXISTS操作符用于检查子查询所返回的行的存在性。虽然EXISTS也可以在非关联子查询中使用，但是EXISTS更常用的用法是用于关联子查询中。
下面这个例子使用EXISTS检索那些负责管理其他员工的员工记录：
SELECT employee_id,last_name
FROM employees outer
WHERE EXISTS
(SELECT employee_id FROM  employees inner
WHERE inner.manager_id=outer.emplyee.id);
由于EXISTS只是检查子查询返回的行的存在性，因此查询不必返回一列；可以只返回一个常量值。这样可以提高查询的性能。下面这个查询对上面那个例子进行了重写：子查询现在只是简单地返回常量值1：
SELECT employee_id,last_name
FROM employees outer
WHERE EXISTS
(SELECT  1  FROM  employees inner
WHERE inner.manager_id=outer.emplyee.id);
在关联子查询中使用NOT  EXISTS
SELECT product_id ,name
FROM products outer
WHERE NOT EXISTS
(SELECT 1
FROM purchases inner
WHERE inner.product_id=outer.product_id);
EXISTS和NOT  EXISTS与IN 和NOT IN的比较：
EXISTS只是检查行的存在性，而IN则要检查实际值的存在性。
通常来讲，EXISTS的性能都比IN高，因此应该尽可能地使用EXISTS,而不是IN。
在编写使用NOT EXISTS和NOT IN的查询时必须谨慎。当一个值列表包含一个空值时，NOT EXISTS就返回true,而NOT IN则返回false。
下列使用了NOT EXISTS，检索那些在products 表中没有任何产品的产品类型：
SELECT product_type_id,name
FROM product_types outer
WHERE NOT EXISTS
(SELECT 1
FROM products inner
WHERE inner.product_type_id=outer.product_type_id);

PRODUCT_TYPE_ID     NAME
---------------------------     ----------
        5              Magazine
现在使用NOT IN 重写上面的例子：
SELECT product_type_id,name
FROM product_types
WHERE product_type_id   NOT IN
(SELECT   product_type_id  FROM products)
No row selected.
之所以没有返回行，是因为子查询返回product_type_id值的列表，其中包含一个空值。而产品的product_type_id是空值。因此，外部查询中的NOT IN操作符返回false,因此最终没有返回任何行。这个问题可以使用NVL()函数将空值转换为一个值解决
SELECT product_type_id,name
FROM product_types
WHERE product_type_id   NOT IN
(SELECT   NVL(product_type_id ,0) FROM products);
这一次返回了记录：
PRODUCT_TYPE_ID     NAME
---------------------------     ----------
        5              Magazine


6.6编写嵌套子查询
嵌套子查询就是位于SELECT、UPDATE或DELETE语句内部的查询。
应该尽量少使用嵌套子查询的技术，因为使用表连接时，查询的性能更高。
6.7编写包含子查询的UPDATE和DELETE语句
在UPDATE语句中，可以将新列的值设置为单行子查询返回的结果。
UPDATE employees
SET  salary=
(SELECT  AVG(high_salary)
FROM  salary_grades)
WHERE employee_id=4;
在DELETE语句的WHERE子句中，可以使用子查询返回的结果。
DELETE FROM employees
WHERE salary >
(SELECT AVG(high_salary)
FROM salary_grades);

7高级查询

7.8使用分析函数
7.8.2使用评级函数
评级函数(ranking function)用于计算等级、百分点、n分片等。
RANK() 和DENSE_RANK()函数可以计算数据项在分组中的排名。这两个函数之间的区别在于处理相等数据项的方式：RANK()在出现等级相同的元素时就将排名中的位置留出来，而DENSE_RANK()则不是。例如，如果根据产品类型评定销售等级，两种产品类型并列第一名，那么RANK()将这两种类型都设置为第一名，而下一个产品类型是第三名。DENSE_RANK()也把这两个类型全部设为第一名，而下一个产品类型则是第二名。
在需要将分组划分为子分组时，可以将PARTITION BY子句和分析函数结合起来使用。

使用ROW_NUMBER()函数
ROW_NUMBER()从1开始，为每一条分组记录返回一个数字。下面这个查询展示了ROW_NUMBER()的用法 ：
SELECT
prd_type_id, SUM(amount),
ROW_NUMBER()  OVER   (ORDER  BY  SUM(amount)  DESC)  AS  row_number
FROM  all_sales
WHERE  year=2003
GROUP  BY prd_type_id
ORDER  BY  prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) ROW_NUMBER
------------------- ------------------- -------------------
     1                   888 2
     2   111 5
3   333     3
4   222     4
5 1