oracle用子查询将行数据,48.Oracle数据库SQL开发之 子查询——编写单行子查询

48.Oracle数据库SQL开发之 子查询——编写单行子查询

单行子查询不向外部的SQL语句返回结果，或者只返回一行。

1.  在WHERE子句中使用子查询

子查询可以放在另一个查询的WHERE子句中，执行如下：

store@PDB1> select first_name,last_name fromcustomers where customer_id=(select customer_id from customers wherelast_name='Brown');

FIRST_NAME LAST_NAME

---------- ----------

John    Brown

2.  使用其他单行操作符

在单行子查询中个，可以使用其他比较操作符，例如<>,,<=,>= 。

store@PDB1>select product_id,name,price from products where price > (select avg(price)from products);

PRODUCT_ID NAME                                PRICE

---------- ----------------------------------------

1 Modern Science                  19.95

2 Chemistry                                 30

3 Supernova                            25.99

5 Z Files                           49.99

3.  在HAVING子句中使用子查询

HAVING子句的作用是对行组进行过滤。外部查询的HAVING子句也可以使用子查询。

执行如下，检索那些平均价格低于同类产品组平均价格最大值的产品的PRODUCT_TYPE_ID和平均价格：

store@PDB1> select product_type_id,avg(price) fromproducts group by product_type_id having avg(price)

PRODUCT_TYPE_ID AVG(PRICE)

--------------- ----------

1    24.975

3     13.24

4     13.99

13.49

4.  在FROM子句中使用查询(内联视图)

在外部查询的FROM子句中也可以使用子查询，这种子查询称为内联视图(inline view)

store@PDB1>select product_id from (select product_id from products where product_id <3);

PRODUCT_ID

----------

1

2

如下，在外部查询中从products表中检索product_id和price列，在子查询中检索一种产品已经被购买的次数：

store@PDB1> selectprds.product_id,price,purchases_data.product_count from products prds,(selectproduct_id,count(product_id) product_count from purchases group by product_id)purchases_data where prds.product_id = purchases_data.product_id;

PRODUCT_ID  PRICEPRODUCT_COUNT

---------- ---------- -------------

1     19.95                   4

2        30                  4

3     25.99                   1

子查询从purchases表中检索出product_id和COUNT(product_id),并将其返回给外部的查询。

5.  可能碰到的错误

5.1         单行子查询最多返回一行

如果子查询返回多行，就会出现错误。

store@PDB1> select product_id,name from productswhere product_id = (select product_id from products where name like '%e%');

select product_id,name from products whereproduct_id = (select product_id from products where name like '%e%')

*

ERROR at line 1:

ORA-01427: single-row subquery returns morethan one row

5.2         子查询不能包含ORDER BY 子句

子查询不能包含ORDER BY 子句。相反，任何排序都必须在外部查询中完成。例如：

store@PDB1>select product_id,name,price from products where price >(select avg(price)from products) order by product_id desc;

PRODUCT_ID NAME                                PRICE

---------------------------------------- ----------

5 Z Files                           49.99

3 Supernova                            25.99

2 Chemistry                                 30

1 Modern Science                  19.95