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
分享到:
相关推荐
ES6是下一代JavaScript语言标准的统称,每年6月发布一次修订版,迄今为止已经发布了3个版本,分别是ES2015、ES2016、ES2017。《ES6标准入门(第3版)》根据ES2017标准,详尽介绍了所有新增的语法,对基本概念、设计...
第3版增加了超过30%的内容,完全覆盖了ES2017标准,相比第2版介绍了更多的语法点,还调整了原有章节的文字表达,充实了示例,论述更准确,更易懂易学。仅供学习交流。 目录 0. 前言 1. ECMAScript 6简介 2. let 和 ...
PMP章节练习解析(第1-3章含20题).pdf PMP章节练习解析(第13章含21题).pdf PMP章节练习解析(第4章含30题).pdf PMP章节练习解析(第5章含18题).pdf PMP章节练习解析(第6章含23题).pdf PMP章节练习...
3.第3章文档类型定义 4.第4章数据建模与XML 5.第5章文档对象模型 6.第6章SAX 1.0:XML简易API 7.第7章命名空间和模式 8.第8章链接和查询 9.第9章转换XML 10.第10章XML和数据库 11.第11章服务器到服务器 12.第12章...
很好的C#的ManagedDirectX的书啊,可惜我找了很久很久才找到这么个资源 还有这可是文字版,PDF版清晰度没的说啦 ...可惜只有1-6章 关于分数,分数应该不是很多吧,我也要去下载人家的资源,要用分的。。
第三部分“排序”(第6~11章)按章节顺序分别讨论了基本排序方法(如选择排序、插入排序、冒泡排序、希尔排序等)、快速排序方法,归并和归并排序方法、优先队列与堆排序方法、基数排序方法以及特殊目的排序方法,...
2015届高考物理二轮复习必备章节检测 第6章 检测3 机械能守恒定律及其应用
第三章详细介绍了 6G 无线通信网络的多种使能技术,包括空口与传输技术、新的网络架构;6G 新的范式转移,即全覆盖、全频谱、全应用、强安全在第四章介绍;最后,第五章对全文做出总结。同时,在第六章附录部分给出...
完整版 Java开发实训课程系列-Java高级应用编程 第3章 文件与流(共53页).ppt 完整版 Java开发实训课程系列-Java高级应用编程 第4章 多线程编程(共29页).ppt 完整版 Java开发实训课程系列-Java高级应用编程 第5章...
此JavaScript权威指南(第6版)(中文版)pdf共分四大部分,一共22个章节,非常适合入门学习。内容相当丰富,欢迎下载; 第一部分为:javascript语言核心;第二部分为:客户端Javascript;第三部分为:javaScript核心参考;第四...
第二版 Android SDK开发范例大全教程源码,由于本人上传大小有限,目前只能到3-6章节,如需要全部教程源码着,留下邮件名,我有时间给兄弟们发!!!
《软件工程导论》(第6版)1-13章课后习题答案
很有趣的一本java书~保证此版本是清晰的。。
Python核心编程(第二版).part3.rar高清版带章节目录结构共6个压缩文件
POWERLINK讲解 分章节 第22章
第3章(随机信号)视需要情况可以作复习性讲述。第二部分(第6章~第10章)主要论述数字通信、模拟信号的数字传输和数字信号的最佳接收原理。由于技术的不断发展和创新,数字调制和数字带通传输的内容非常丰富,将其放在...
第6章 Nand Flash控制器 第7章 时钟与电源管理 第8章 直接存储器存取 第10章 PWM及定时器 第11章 UART 第14章 中断控制器 第16章 ADC和触摸屏接口 第17章 实时时钟 第18章 看门狗定时器 第19章 MMC/SD/SDIO控制器 第...
这是我通过mooc慕课平台,选择学习的一个python课程,其中涉及... 第三章 基本语句应用 3 第四章 字符串 3 第五章 组合数据类型 6 第六章 输入与输出 6 第七章 控制与结构 9 第八章 函数 6 等
本课件对应汤第三版版计算机操作系统教材,章节完整,内附1-6章课后答案