MySql：如何从不同的表中获取SUM值？

Question

对于每个订单，我想要得到产品的总数和付款的总数，以便得出余额。

以下是表格的简化版本：

ORDERS
--------------------
|ord_id|customer_id|
--------------------
|     1|        XYZ|
|     .|          .|
|     .|          .|
|     .|          .|
--------------------

ORDER_DETAILS
-----------------------------------------
|det_id|ord_id|product_id|quantity|price|
-----------------------------------------
|     1|     1|    AAA001|       3|   30|
|     2|     1|    BBB002|       2|    5|
|     .|     .|         .|       .|    .|
|     .|     .|         .|       .|    .|
|     .|     .|         .|       .|    .|
-----------------------------------------

PAYMENTS
----------------------
|pay_id|ord_id|amount|
----------------------
|     1|     1|    10|
|     2|     1|    20|
|     3|     1|    10|
|     .|     .|     .|
|     .|     .|     .|
|     .|     .|     .|
----------------------

这个查询并不能正确返回支付的值，只有在产品数量等于支付数量时才能正确获取支付的值：

SELECT o.ord_id, SUM(quantity * price) AS total_order, SUM(amount) AS total_payments
FROM orders AS o 
INNER JOIN order_details AS d ON o.ord_id = d.ord_id
INNER JOIN payments AS p ON o.ord_id = p.ord_id
GROUP BY o.ord_id

这是预期结果：

-----------------------------------
|ord_id|total_order|total_payments|
-----------------------------------
|     1|        100|            40|
|     .|          .|             .|
|     .|          .|             .|
|     .|          .|             .|
-----------------------------------

提前感谢您。

- Leonardo

Answer 1

将这两个查询分别执行，然后将结果合并。在逻辑上，这对我来说更有意义:

SELECT
    ot.ord_id, 
    ot.order_total,
    op.order_paid
FROM
    (
        SELECT
            ord_id,
            SUM(price * quantity) AS order_total
        FROM
            ORDER_DETAILS
        GROUP BY
            ord_id
    ) AS ot
    INNER JOIN (
        SELECT
            ord_id,
            SUM(amount) AS order_paid
        FROM
            PAYMENTS
        GROUP BY
            ord_id
    ) AS op ON (op.ord_id = ot.ord_id)
;

…

 ord_id | order_total | order_paid 
--------+-------------+------------
      1 |         100 |         40
(1 row)

- Thanatos

Answer 2

尝试按det_id分组，并使用WITH ROLLUP子句：

SELECT o.ord_id, SUM(quantity * price) AS total_order, SUM(amount) AS total_payments
FROM orders AS o 
INNER JOIN order_details AS d ON o.ord_id = d.ord_id
INNER JOIN payments AS p ON o.ord_id = p.ord_id
GROUP BY o.ord_id, o.det_id WITH ROLLUP

- Tudor Constantin

---

我在使用WITH修饰符时遇到了语法错误（MySql版本：5.1.41）。我查看了文档（http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html），但是我发现子句是正确的。 - Leonardo