开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> Oracle 子查询 -> 正文阅读

[大数据]Oracle 子查询

Oracle 子查询

子查询：嵌入其他查询种的查询

注意：

查询（Query）:任何SQL语句。不过，这个是术语通常用于指SELECT语句。

1、通过子查询过滤

本节演示的所有表均为关系表。订单数据存储在两个表中。在Order表中，每个订单被存储在单独一行中，其中包括订单号、顾客ID和订单日期。各个订单项存储在相关的orderitems表中。order表没有存储顾客信息，他只存储顾客ID。实际的顾客信息存储在customers表中。

现在假设想要订购商品TNT2的所有顾客的列表。该如何检索信息？下面给出了操作步骤。

检索包括商品TNT2的所有订单的订单号。
检索具有在上一步中返回的订单号中列出的订单的所有顾客的ID。
针对上一步中返回的所有顾客ID检索相关顾客信息。

其中每一步都可以作为一个单独的查询来执行。通过这样做，可以使用由一条SELECT语句返回的结果来充当下一条SELECT语句的WHERE子句的条件。

还可以使用子查询把全部3个查询结合成单独一条语句。

目前，第一条SELECT语句应该是不言自明的。它以prod_id是TNT2为条件在所有订单项中检索order_num列。输出中列出了两个包含该商品的订单

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

在这里插入图片描述

下一步是检索与订单20005和20007关联的顾客ID。使用第7章中讲解的IN子句可以创建一条SELECT语句，如下：

SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);

在这里插入图片描述

现在，通过把第一个查询（返回订单号的查询）变成子查询，从而把两个查询结合起来。查看下面的SELECT语句：

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

在这里插入图片描述

注意：

子查询总是从最内层的SELECT语句开始处理，并向外执行.

首先，它运行子查询：

SELECT order_num FROM orderitems WHERE prod_id='TNT2'

这个查询返回两个订单号20005和20007。然后，将以IN运算符所需要的逗号分隔的格式把这两个值传递给外层查询的WHERE子句。外层查询现在变成：

SELECT cust_id FROM orders WHERE order_num IN (20005,20007)

提示：格式化SQL

包括子查询的SQL语句可能难以阅读和调试，尤其是当他们的复杂性增加时。如这里所示，把查询分解到多行上并且对这些行适当地进行缩进，可以极大地简化子查询地处理。

现在就有了订购商品TNT2的所有顾客的ID。下一步是检索与所有这些ID关联的顾客信息。用于检索两个列的SQL语句如下：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001,10004);

无须硬编码那些顾客ID，可以把这个WHERE子句转变成另一个子查询：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2'));

在这里插入图片描述

为了执行这条SELECT语句，Oracle实际上不得不执行3条SELECT语句。最内层的子查询返回订单号的列表，然后将把它们用作该子查询上面的子查询的WHERE子句。后一个子查询返回顾客ID的列表，它们将用作顶级查询的WHERE子句。顶级查询实际上返回我们想要的数据。

注意：

随着进一步嵌套查询，性能将开始退化。

警告：列必须匹配

在WHERE子句中使用子查询时（如这里所示），要确保SELECT语句具有与WHERE子句中相同的列数。通常，子查询将返回单个列，并且与单个列匹配，但是如果需要也可以使用多个列。

尽管子查询通常与IN运算符结合使用，但是它们也可用于测试相等性（使用=）、不等性（使用<>）等。

注意：

Oracle允许在WHERE子句内嵌套最多255个层级的子查询（尽管很难找到需要这样做的情况）。

警告：子查询和性能

这里所示的代码会检索到我们想要的结果，不过使用子查询并不总是执行这类数据检索的最高效方式。

2、把子查询作为计算字段

使用子查询的另一种方式是创建计算字段。假设你想显示由customers表中的每一位顾客所下的订单总数。订单与相应的顾客ID一起存储在orders表中。

要执行该操作，可以按照以下步骤。

1．从customers表中检索顾客的列表。

2．对于检索到的每一位顾客，统计orders表中关联订单的数量。

可以使用SELECT COUNT(*)统计表中的行，以及通过提供一个WHERE子句来过滤特定的顾客ID，可以只统计该顾客的订单。例如，下面的代码用于统计由顾客10001所下的订单数量

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;

要为每位顾客执行COUNT()计算，可以把COUNT用作子查询。看看下面的代码：

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
       FROM orders
       WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

在这里插入图片描述

这条SELECT语句为customers表中的每一位顾客返回3列：cust_name、cust_state和orders。orders是一个计算字段，由圆括号中提供的子查询设置。每查询到一位顾客就执行子查询一次。在这个示例中，子查询会执行5次，因为检索到5位顾客。

相关子查询 （Correlated Subquery）：引用外部查询的子查询。

提示：增量式地构建带有子查询的查询

测试和调试带有子查询的查询可能比较棘手，尤其是当这些语句变得越来越复杂时。构建（和测试）带有子查询的查询的最安全方式是采用增量式方法，这与Oracle处理它们的方式非常相似。首先构建最内层的查询，然后构建和测试带有硬编码的数据的外层查询，并且仅当验证它会运行之后才嵌入子查询。然后再次测试它，并且为每个其他的查询保持重复这些步骤。在构造查询时这将会花费稍长一点的时间，但它可以在以后为你节省许多的时间（当你尝试搞清楚查询为什么不能正确运行时），并且显著提高它们初次就能正确运行的可能性。

注意：