Oracle子查询

作者：--    发布时间：2019-11-20

在本教程中，您将了解有助于构建更多可读查询的oracle子查询，并可在不使用复杂联接或联合的情况下编写查询。

oracle子查询简介

子查询是嵌套在另一个语句(如select，insert，update或delete)中的select语句。 通常，可以在任何使用表达式的地方使用子查询。

来看看下面这个子查询的例子。

以下查询使用max()函数从products表中返回产品的最高标价：

select
    max( list_price )
from
    products;

执行上面查询语句，得到以下结果 -

要查询最贵的产品的详细信息，可在以下查询中使用上面的查询出的标价(8867.99)：

select
    product_id, product_name, list_price
from
    products
where
    list_price = 8867.99;

执行上面查询语句，得到以下结果 -

正如上面所看到的，我们需要分别执行两个查询来获取最贵的产品数据信息。 通过使用子查询可以将第一个查询嵌套到第二个查询中，如以下查询所示：

select
    product_id,  product_name, list_price
from
    products
where
    list_price = (
        select
            max( list_price )
        from
            products
    );

执行上面查询语句，得到以下结果 -

在此示例中，检索最高价格的查询是子查询，选择详细产品数据的查询是外部查询。子查询嵌套在外部查询中。请注意，子查询必须出现在括号内。

oracle分两步评估(计算)上面的整个查询：

• 首先，执行子查询。
• 其次，在外部查询中使用子查询的结果作为条件查询数据。

嵌套在select语句的from子句中的子查询称为内联视图。请注意，其他rdbms(如mysql和postgresql)使用术语派生表 而不是内联视图。

嵌套在select语句的where子句中的子查询称为嵌套子查询。

子查询可以包含另一个子查询。oracle允许在顶级查询的from子句中使用无限数量的子查询级别，并在where子句中使用多达255个子查询级别。

oracle子查询的优点

下面是子查询的主要优点：

• 提供一种替代方法来解决查询需要复杂联接和联合的数据。
• 使复杂的查询更具可读性。
• 允许以一种可以隔离每个部分的方式来构建复杂的查询。

oracle子查询示例

1. oracle select子查询示例

以下声明按产品类别返回产品名称，标价和平均标价：

select
    product_name,
    list_price,
    round(
        (
            select
                avg( list_price )
            from
                products p1
            where
                p1. category_id = p2.category_id
        ),
        2
    ) avg_list_price
from
    products p2
order by
    product_name;

执行上面查询语句，得到以下结果 -

在这个例子中，在select子句中使用了一个子查询来获得同一分类的产品的平均标价。 oracle为外部查询选择的每一行评估(计算)子查询。

这个子查询被称为相关的子查询，我们将在下一个教程中详细介绍。

2. oracle from子句中的子查询示例

select语句的from子句中的子查询被称为内联视图，它具有以下语法：

select
 *
from
 (subquery) [as] inline_view;

例如，以下语句返回最高金额的前10个订单：

select
    order_id, order_value
from
    (
        select
            order_id,
            sum( quantity * unit_price ) order_value
        from
            order_items
        group by
            order_id
        order by
            order_value desc
    )
where
    rownum <= 10;

执行上面查询语句，得到以下结果 -

在这个查询语句中：

• 首先，子查询返回由order_value按降序排序的order_id和order_value的列表。
• 然后，外部查询检索列表顶部的前10行。

3. 带有比较运算符的子查询示例

使用比较运算符，即，>，>=，<，<=，<>，=的子查询通常包含聚合函数，因为集合函数返回可用于比较的单个值和外部查询。

例如，以下查询查找标价大于平均标价的产品。参考以下查询语句 -

select
    product_id,
    product_name,
    list_price
from
    products
where
    list_price > (
        select
            avg( list_price )
        from
            products
    )
order by
    product_name;

执行上面的查询语句，得到类似下面的结果 -

该查询的工作原理如下：

• 首先，子查询返回所有产品的平均标价。
• 其次，外部查询获取标价大于子查询返回的平均标价的产品。

4. 具有in和not in运算符的oracle子查询

使用in运算符的子查询通常返回零个或多个值的列表。子查询返回结果集后，外部查询使用它们作为匹配条件。

例如，以下查询查找2017年销售额高于100w的销售员，参考以下查询语句：

select
    employee_id,
    first_name,
    last_name
from
    employees
where
    employee_id in(
        select
            salesman_id
        from
            orders
        inner join order_items
                using(order_id)
        where
            status = 'shipped'
        group by
            salesman_id,
            extract(
                year
            from
                order_date
            )
        having
            sum( quantity * unit_price )  >= 1000000  
            and extract(
                year
            from
                order_date) = 2017
            and salesman_id is not null
    )
order by
    first_name,
    last_name;

执行上面查询语句，得到以下结果 -

oracle分两步评估(计算)上面示例中的查询：

• 首先，子查询返回一个销售人员的销售额大于或等于100万的列表。
• 其次，外部查询使用销售员id列表来查询雇员表中的数据。

以下语句查询所有2017年尚未下订单的客户：

select
    name
from
    customers
where
    customer_id not in(
        select
            customer_id
        from
            orders
        where
            extract(
                year
            from
                order_date) = 2017

    )
order by
    name;

执行上面查询语句，得到以下结果 -

在这个示例的查询中，

• 首先，子查询返回2017年下单一个或多个订单的客户的id列表。
• 其次，外部查询返回带有不在子查询返回的列表中的id的客户。

在本教程中，您已经了解了有关oracle子查询，它提供了一种替代方法来构建更具可读性的查询，而无需使用复杂的联接或联合。