mysql - 在 A 行上左连接多个 B 行时如何对 A 行列求和

Question

我相信这个问题已经出现过好几次了，但我还没有在 Stack 上的答案中找到简单的解决方案。

我的问题是，当我有 1:n 关系并在 A 表行上使用 LEFT JOIN(其中一个 A 行可以有多个 B 行)时，如何获取 A 中的值之和？

假设我有 A 表 orders 和 B 表 transactions 用于 orders。一个订单可以有多个交易。下面是一个查询示例:

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `price` decimal(10,2) DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `orders` (`id`, `price`)
VALUES
    (1, 330.00),
    (2, 800.00);

INSERT INTO `transactions` (`id`, `order_id`, `amount`)
VALUES
    (1, 1, 330.00),
    (2, 2, 200.00),
    (3, 2, 200.00),
    (4, 2, 200.00);

然后当我尝试总结订单价格时:

SELECT o.id, sum(o.price) from orders o
LEFT JOIN transactions tr ON o.id = tr.order_id

sum(o.price) 的结果是 2730。它应该是1130 (330+800)。相反，由于 order(2) 有 3 个 transaction，因此结果中 LEFT JOIN 生成的该订单有 3 个 transaction 行，因此计算结果为330+800*3。我尝试使用 GROUP BY o.id 但总和可能是在 GROUP BY ( http://sqlfiddle.com/#!9/82bd8/8/0 )

之前计算的

如何使总和显示正确的值但保留 LEFT JOIN？

Answer 1

您得到 2730，因为您的连接与您的主数据集不是线性的。虽然我建议的答案不是最有效的方法，但我相信它可以解决您的情况。

SELECT 
    orders.id,
    (SELECT SUM(price) FROM orders) AS 'price'
FROM 
    orders
LEFT JOIN
    transactions ON
        transactions.order_id = orders.id