SQL之子查询

1、案例

案例1:

OrderItems表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num

OrderItems表

Orders表

【问题】使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

--T1：
select cust_id
from Orders
left join OrderItems
on OrderItems.order_num = Orders.order_num
where item_price >= '10'

--PS:同一顾客可能会多次购买十美元及以上的商品，故需要用DISTINCT去重

--T2：
SELECT DISTINCT cust_id
FROM Orders
WHERE order_num IN (
    SELECT order_num
    FROM OrderItems
    WHERE item_price >= '10')

【示例结果】返回顾客id cust_id

案例2:

表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date

OrderItems表

Orders表

【问题】

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

--t1:
select
  distinct cust_id,
  order_date
from
  Orders
  left join OrderItems on Orders.order_num = OrderItems.order_num
where
  prod_id = 'BR01'
order by
  order_date 

--where版本
select
  cust_id,
  order_date
from
  Orders o,
  OrderItems oi
where
  prod_id = 'BR01'
  and o.order_num = oi.order_num
order by
  order_date ； 

--子查询版本
select
  cust_id,
  order_date
from
  Orders
where
  order_num in (
    select
      order_num
    from
      OrderItems
    where
      prod_id = 'BR01'
  )
order by
  order_date;

--左连接版本
select
  cust_id,
  order_date
from
  Orders o
  LEFT JOIN OrderItems oi ON o.order_num = oi.order_num
where
  prod_id = 'BR01'
order by
  order_date;

--自然连接版本
select
  cust_id,
  order_date
from
  Orders
  NATURAL JOIN OrderItems
where
  prod_id = 'BR01'
order by
  order_date;

--内连接 类似where
select
  cust_id,
  order_date
from
  Orders o
  inner JOIN OrderItems oi on o.order_num = oi.order_num
  and prod_id = 'BR01'
order by
  order_date;

--join using 类似自然连接
select
  cust_id,
  order_date
from
  Orders # 相当于自然连接对相同的列进行连接
  join OrderItems using(order_num)
where
  prod_id = 'BR01'
order by
  order_date;

【示例结果】返回顾客id cust_id和定单日期order_date。

案例3：

你想知道订购 BR01 产品的日期，有表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；Customers表含有cust_email 顾客邮件和cust_id顾客id

OrderItems表

Orders表

Customers表代表顾客信息，cust_id为顾客id，cust_email为顾客email

【问题】返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

select
  cust_email
from
  Customers
where
  cust_id in (
    select
      cust_id
    from
      Orders
    where
      order_num in (
        select
          order_num
        from
          OrderItems
        where
          prod_id = 'BR01'
      )
  )

【示例结果】

返回顾客email cust_email

案例4：

我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。

Orders表订单号：order_num、顾客id：cust_id

【问题】

编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

提示：你之前已经使用 SUM()计算订单总数。

select
  a.cust_id,
  sum(b.total_o) as total_ordered
from
  Orders a
  left join (
    select
      order_num,
      sum(item_price * quantity) as total_o
    from
      OrderItems
    group by
      order_num
  ) b on a.order_num = b.order_num
group by
  cust_id
order by
  total_ordered desc

--等值连接
select
cust_id,
(select
 SUM(item_price*quantity)
FROM OrderItems a
WHERE a.order_num=b.order_num)total_ordered
from Orders b
ORDER BY total_ordered DESC

【示例结果】返回顾客id cust_id和total_order下单总额