mysql - 多于 1 行从 SELECT 内部的 SELECT 返回

问题描述

我正在尝试创建一个查询来查找每个客户欠公司的总金额。它是GROUP BY customerNumber在子查询中造成问题。

SELECT customerName,
       customers.customerNumber,
       SUM(quantityOrdered * priceEach) - ( SELECT SUM(amount) AS MoneyPayed FROM payments GROUP BY customerNumber ) AS AmountOwed
FROM payments
INNER JOIN customers ON payments.customerNumber = customers.customerNumber
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
INNER JOIN  orderdetails ON  orders.orderNumber = orderdetails.orderNumber
GROUP BY customerNumber;

我要链接的表是payments和orderdetails。

当我摆脱时，GROUP BY我会得到否定的结果，因为SUM从SUM(quantityOrdered * priceEach).

如何更改此设置，以便我可以从付款中返回多行以从SUM(quantityOrdered * priceEach)订单详细信息表中减去。

链接到 DB，因为 StackOverflow 不允许我发布图片

感谢您的帮助，如果格式不好，对不起，这是我的第一篇文章。

标签： mysqljoinaggregate-functionsworkbench

解决方案

您将需要几个子查询来满足您的要求。让我们分解一下。

首先，您需要每个客户的订单总价值。您非常接近正确的查询。它应该是

               SELECT orders.customerNumber,
                      SUM(orderdetails.quantityOrdered * orderdetails.priceEach) owed
                 FROM orders
                 JOIN orderdetails ON  orders.orderNumber = orderdetails.orderNumber
             GROUP BY orders.customerNumber

这个子查询的结果集给出了 customerNumber 和 owed，即欠款。请注意，orders::orderdetails 是 one::many 关系，因此我们确定每个细节只计算一次，因此 SUM 是正确的。

接下来我们需要每个客户支付的金额。这个子查询相当简单。

               SELECT customerNumber,
                      SUM(amount) paid
                 FROM payments
                GROUP BY customerNumber

现在对于您的问题中缺少的操作：我们需要将这两个子查询加入您的客户表。

SELECT customers.customerName, customers.customerNumber
       owed.owed - paid.paid balance
  FROM customers
  LEFT JOIN (
               SELECT orders.customerNumber,
                      SUM(orderdetails.quantityOrdered * orderdetails.priceEach) owed
                 FROM orders
                 JOIN orderdetails ON  orders.orderNumber = orderdetails.orderNumber
             GROUP BY orders.customerNumber
       ) paid ON customers.customerNumber = paid.customerNumber
  LEFT JOIN (
               SELECT customerNumber,
                      SUM(amount) paid
                 FROM payments
                GROUP BY customerNumber
       ) owed ON customers.customerNumber = owed.customerNumber

看看这是如何工作的？我们加入一个表和两个子查询。对于表中的每一行，每个子查询有 0 行或 1 行，因此我们不需要在外部查询中使用 SUM 或 GROUP BY。

只剩下一个复杂的问题：如果客户从未支付过任何费用怎么办？那么在 LEFT JOIN 操作之后，paid.paid 的值就会为 NULL。这将强制的值为owed - paidNULL。因此，我们需要在 SELECT 语句中使用更多智能来产生正确的总和。

SELECT customers.customerName, customers.customerNumber
       COALESCE(owed.owed,0) - COALESCE(paid.paid,0) balance
       ...

COALESCE(a,b)等价于if a is not null then a else b。

专业提示在带有 JOIN 操作的查询或子查询中，总是提及table.column而不是仅仅提及column. 下一位处理您的查询的人会感谢您。

mysql - 多于 1 行从 SELECT 内部的 SELECT 返回

问题描述

解决方案

推荐阅读