mysql - mysql:每个子查询结果的返回列
问题描述
我有 3 个表:customers
、orders
和order-items
。该问题要求编写一个 SELECT 语句,该语句将返回一个表
- 客户的电子邮件地址
- 该客户的订单数量
- 以及每个订单的总成本
前两点相当直截了当:
SELECT email_address,
(SELECT SUM(order_id)
FROM orders
WHERE orders.customer_id = cust.customer_id) as "order_count"
FROM customers as cust
而且我知道我可以order_total
像这样获得每个订单:
SELECT order_id,
(SELECT SUM((item_price - discount_amount) * quantity)
FROM order_items
WHERE order_items.order_id = ord.order_id) as "order_total"
FROM orders AS ord
但是,如果我尝试将它们结合起来并执行以下操作:
SELECT email_address,
(SELECT SUM(order_id)
FROM orders AS ord
WHERE ord.customer_id = cust.customer_id) as "order_count",
(SELECT(SELECT SUM((item_price - discount_amount) * quantity)
FROM order_items
WHERE order_items.order_id = ord.order_id)
FROM orders AS ord
WHERE ord.customer_id = cust.customer_id) as "order_total"
FROM customers as cust;
我得到:
子查询返回超过 1 行
我明白为什么我会收到这个错误。因为是的,子查询确实返回多行。我想要的是将这些行中的每一行的值放在另一列中(至少这是我解释要问的问题)。例如:
email_address | order_count | order1_total | order2_total | etc...
============================================================================
john_doe@foo.com | 2 | $100 | $200 | NULL
DB 的表如下所示:
客户(客户 ID,电子邮件地址)
订单(订单 ID,客户ID)订单项目(项目 ID,订单
ID,项目价格,折扣金额,数量)
作业中提出的问题:(
我认为措辞真的很糟糕,但我知道什么?):)
编写一个 SELECT 语句,为每个具有这些列的订单的客户返回一行:
- 客户表中的 email_address
- 订单数量统计
- 每个订单的总金额(提示:首先,从价格中减去折扣金额。然后,乘以数量。)
仅返回客户有超过 1 个订单的行。按行项目金额的总和对结果集进行降序排序。
解决方案
您可以通过使用以下聚合来避免此类相关子查询GROUP BY
:
SELECT
c.email_address,
COUNT(DISTINCT o.order_id) AS num_orders,
COALESCE(SUM(oi.quantity * (oi.item_price - oi.discount_amount)), 0) AS total_amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
INNER JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY
c.customer_id,
c.email_address;
推荐阅读
- java - resttemplate.getforObject(url,pojo.class) getting null in java 11, but it fetch data in java 1.8
- sql - 希望这一切都产生一个 SQL Server 查询
- javascript - 如何在到期前知道日期
- vb.net - oledb 连接中的线程,我不知道如何在这种情况下创建线程。它很慢
- qt - 带有单例 theme.qml 的 QML 样式化按钮
- sharepoint-online - Upload a file to sharepoint using postman
- html - Angular Table with expandable rows, subscribe and get data on expand row
- python - Draw a white line across a transparent image in Python
- excel - Save filter view Excel 365
- python - I am trying to create a class which takes values of variables while looping through a textfile