问题:
查找每个客户的总订单金额和总付款的净余额。
涉及到4个表:OrderDetails
、Orders
、Payments
和Customer
。
订单总额=订单数量*价格,单位为OrderDetails
总付款=同一订单不同付款的总和。
Customers
通过CustomerNumber
链接到Payments
和Orders
。Orders
通过OrderNumber
链接到OrderDetails
。
我尝试使用INNER JOIN
函数连接这4个表。
SELECT
c.customername,
SUM(od.quantityordered * od.priceeach) - SUM(p.amount) AS Net_Balance
FROM
(
(
orderdetails od
INNER JOIN orders o ON od.ordernumber = o.ordernumber
)
INNER JOIN customers c ON o.customernumber = c.customernumber
)
INNER JOIN payments p ON c.customernumber = p.customernumber
GROUP BY c.customername;
几乎每个客户的预期结果都应该是0。
具体地说,显示的总付款数乘以每个订单的付款次数。
有谁有办法救我的命吗?
发布于 2019-04-01 04:42:27
这是处理N-M关系时的典型问题。要解决这个问题,一种解决方案是将聚合移动到子查询:
SELECT c.customername, o.amt - p.amt AS Net_Balance
FROM customers c
INNER JOIN (
SELECT ord.customernumber, SUM(det.quantityordered * det.priceeach) as amt
FROM orders ord
INNER JOIN orderdetails det ON ord.ordernumber = det.ordernumber
GROUP BY ord.customernumber
) o ON o.customernumber = c.customernumber
INNER JOIN (
SELECT customernumber, SUM(amount) as amt
FROM payments
GROUP BY customernumber
) p ON p.customernumber = c.customernumber
发布于 2019-04-01 04:54:22
SELECT c.customername, SUM(od.quantityordered*od.priceeach) as ordersum , SUM(p.amount) as paymentsum'
这两列的结果是什么?
这是你想要的吗?
https://stackoverflow.com/questions/55445210
复制相似问题