标量子查询

子查询入门
SELECT语句可以嵌套在其他语句中,比如SELECT,INSERT,UPDATE以及DELETE等，
这些被嵌套的SELECT 语句就称为子查询，可以这么说当一个查询依赖于另外一个查询结
果时就可以使用子查询。子查询有两种类型，一种是只返回一个单值的子查询，这时它可以
用在一个单值可以使用的地方，这时子查询可以看作是一个拥有返回值的函数；另外一种是
返回一列值的子查询，这时子查询可以看作是一个在内存中临时存在的数据表。
单值子查询
单值子查询的语法和普通的SELECT 语句没有什么不同，唯一的限制就是子查询的返
回值必须只有一行记录，而且只能有一个列。这样的子查询又被称为标量子查询，标量子查
询可以用在SELECT语句的列表中、表达式中、WHERE 语句中等很多场合。
首先来看一个在SELECT语句列表中使用的最简单的标量子查询。SQL语句如下：
MYSQL,MSSQLServer:
SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT
MAX(FYearPublished) FROM T_Book) AS f4
Oracle:
SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT
MAX(FYearPublished) FROM T_Book) AS f4 FROM DUAL
DB2:
SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT
MAX(FYearPublished) FROM T_Book) AS f4 FROM SYSIBM.SYSDUMMY1
这个SQL语句一共返回四列，第一列是数字1，第二列是数字2，第三列则是一个标量
子查询，它返回最早出版图书的年份，第四列也是一个标量子查询，它返回最晚出版图书的
年份。这里完全可以将标量子查询当成一个普通的列，而且还可以为标量子查询列取一个别
名。
执行完毕我们就能在输出结果中看到下面的执行结果：
f1 f4
1 2 1700 2008
如果一个子查询返回值不止一行记录或者有多个列的话都不能当作标量子查询使用，否
则会出错。比如下面SQL语句是错的：
SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book)
由于这句SQL 语句中的子查询会返回多行记录，所以在执行的时候数据库会提示如下
的错误信息：
子查询返回的值不止一个。

当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
下面的SQL 语句也是错误的：
SELECT 1 AS f1,2,(SELECT MAX(FYearPublished),MIN(FYearPublished) FROM T_Book)
由于这句SQL语句中的子查询会返回包含两列数据的结果集，所以在执行的时候数据库会提示如下的错误信息：
当没有用EXISTS 引入子查询时，在选择列表中只能指定一个表达式。
上面举的例子都是在执行前就能确定是否正确的，但是有的时候一个子查询是否使用正确是要到运行时才能确定的。比如在数据库系统中执行下面的SQL语句：
SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book where FYearPublished<2000)
在数据库系统中执行后这句SQL语句会报错，因为发行日期小于2000年的书有不止一本，
所以子查询的返回结果集也就有不止一条记录。如果我们调整这句SQL 语句，转而查询发行日期小于1750年的书，SQL语句如下：
SELECT 1 AS f1,2,(SELECT FYearPublished FROM T_Book where FYearPublished<1750)
因为数据库中发行日期小于1750年的书恰恰只有一本，所以这句SQL语句能够执行成功，执行结果如下：
f1
1 2 1700
列值子查询
与标量子查询不同，列值子查询可以返回一个多行多列的结果集。这样的子查询又被称
为表子查询，表子查询可以看作一个临时的表，表子查询可以用在SELECT 语句的FROM
子句中、INSERT语句、连接、IN 子句等很多场合。
首先来看一个在FROM子句中使用的最简单的表子查询。SQL语句如下：
SELECT T_Reader.FName,t2.FYearPublished,t2.FName FROM T_Reader,
(SELECT * FROM T_Book WHERE FYearPublished < 1800) t2
这里将T_Reader表和表子查询做交叉连接，并且将“SELECT * FROM T_Book WHERE
FYearPublished < 1800”做为表子查询，还可以为表子查询执行表别名，在SELECT
的列表中也可以使用和表一样的列名引用方式，这与使用一个普通的数据表没有什么区别。

 

 

 

标量子查询

ORACLE允许在select子句中包含单行子查询, 使用标量子查询可以有效的改善性能，当使用到外部连接，或者使用到了聚合函数，就可以考虑标量子查询的可能性

1. 取消外部连接的使用

外部连接的做法：

select a.username,count(*)  from all_users a,all_objects b
where a.username=b.owner(+)
group by a.username;

 改成标量子查询的做法：

select a.username,(select count(*) from all_objects b where b.owner=a.username) cnt
from all_users a;

 PS: 两种做法得到的结果会有些许差别，主要在all_objects没有符合条件的行时, 外部连接的count(*)=1，而标量子查询的count(*)结果=0

select a.username,count(*),avg(object_id) from all_users a,all_objects b
where a.username=b.owner(+)
group by a.username; 

 2. 多个聚合函数的使用技巧

当同时出现count(*)/avg()时，不适合在select子句中调用两次子查询，性能上会受到影响, 可以改用下面两种做法

(1).拼接之后再拆分

select username,to_number(substr(data,1,10)) cnt,to_number(substr(data,11)) avg from
(
select a.username,(select to_char(count(*),'fm0000000009') || avg(object_id) from all_objects b where b.owner=a.username) data
from all_users a
)

 (2).创建对象类型

create or replace type myType as object
(cnt number,avg number);

select username,a.data.cnt,a.data.avg from
(
select username,(select myType(count(*),avg(object_id)) from all_objects b where b.owner=a.username) data
from all_users a
) a;