MySQL sum with group by 给出错误的结果

Question

这是 the SQLFiddle具有架构和数据。

我正在尝试对 2 列求和，一列在父级，另一列在子级。我正在使用的当前查询为我提供了子级别的正确总金额，但由于子级别涉及的另一个一对多关系，父级别的金额加倍。

呃......这是一个糟糕的解释 - 这是英文版本:销售员乔参与了 2 次销售。对于第一次销售，他根据 2 种不同的佣金类型获得 2 组佣金。我试图显示 Joe 的总销售值(value)，以及他适用的拆分的总值(value)。拆分总值(value)很好，但销售值(value)翻了一番，因为我显然分组/加入不正确(请参见下面的最后一个示例)。

这很好:

select sp.person_name, pr.description,
sum(spl.split) as SplitValue
from sale s, product pr, sales_person sp, sales_split spl
where s.product_id = pr.id
and s.id = spl.sale_id
and sp.id = spl.sales_person_id
group by sp.id;

person_name | description | SplitValue
-----------   ----------- | ----------
Joe         | Widget 1    | 50
Sam         | Widget 1    | 10

这也产生了正确的拆分和销售值，但现在为 Joe 显示了 3 行(即第 2 行是第 1 行的副本)- 我只想显示 Joe 的“Widget 1”销售一次，所以不正确:

select sp.person_name, pr.description,
sum(s.sale_value) as SaleValue, sum(spl.split) as SplitValue
from sale s, product pr, sales_person sp, sales_split spl, sales_split_agreement ssa
where s.id = spl.sale_id
and s.product_id = pr.id
and sp.id = spl.sales_person_id
and sp.id = ssa.sales_person_id
and spl.sales_person_id = ssa.sales_person_id
and ssa.id = spl.sales_split_agreement_id
group by sp.id, spl.id;

person_name | description | SplitValue | SaleValue
-----------   -----------   ----------   ---------
Joe         | Widget 1    | 10         | 20
Joe         | Widget 1    | 10         | 20
Joe         | Widget 2    | 30         | 30
Sam         | Widget 1    | 10         | 20

现在重复的行消失了，但是 Joe 的 SaleValue 不正确 - 它应该是 50，而不是 70:

select sp.person_name, pr.description,
sum(spl.split) as SplitValue, sum(s.sale_value) as SaleValue
from sale s, product pr, sales_person sp, sales_split spl, sales_split_agreement ssa
where s.id = spl.sale_id
and s.product_id = pr.id
and sp.id = spl.sales_person_id
and sp.id = ssa.sales_person_id
and spl.sales_person_id = ssa.sales_person_id
and ssa.id = spl.sales_split_agreement_id
group by sp.id;


person_name | description | SplitValue | SaleValue
-----------   -----------   ---------   ----------
Joe         | Widget 1    | 50         | 70
Sam         | Widget 1    | 10         | 20

即我正在查询将产生此结果(即 Joe 的正确 SaleValue 为 50):

person_name | description | SplitValue | SaleValue
-----------   -----------   ---------   ----------
Joe         | Widget 1    | 50         | 50
Sam         | Widget 1    | 10         | 20

任何帮助将不胜感激!

更新 1:

为清楚起见 - 这是来自 fiddle 的架构和测试数据:

CREATE TABLE product
    (`id` int, `description` varchar(12))
;

INSERT INTO product
    (`id`, `description`)
VALUES
    (1, 'Widget 1'),
    (2, 'Widget 2')
;


CREATE TABLE sales_person
    (`id` int, `person_name` varchar(7))
;

INSERT INTO sales_person
    (`id`, `person_name`)
VALUES
    (1, 'Joe'),
    (2, 'Sam')
;


CREATE TABLE sale
    (`id` int, `product_id` int, `sale_value` int)
;

INSERT INTO sale
    (`id`, `product_id`, `sale_value`)
VALUES
    (1, 1, 20.00),
    (2, 2, 30.00)
;

CREATE TABLE split_type
    (`id` int, `description` varchar(6))
;

INSERT INTO split_type
    (`id`, `description`)
VALUES
    (1, 'Type 1'),
    (2, 'Type 2')
;

CREATE TABLE sales_split_agreement
    (`id` int, `sales_person_id` int, `split_type_id` int, `percentage` int)
;

INSERT INTO sales_split_agreement
    (`id`, `sales_person_id`, `split_type_id`, `percentage`)
VALUES
    (1, 1, 1, 50),
    (2, 1, 2, 50),
    (3, 2, 1, 50),
    (4, 1, 1, 100)
;


CREATE TABLE sales_split
    (`id` int, `sale_id` int, `sales_split_agreement_id` int, `sales_person_id` int, `split` int )
;

INSERT INTO sales_split
    (`id`, `sale_id`, `sales_split_agreement_id`, `sales_person_id`, `split`)
VALUES
    (1, 1, 1, 1, 10),
    (2, 1, 2, 1, 10),    
    (3, 1, 3, 2, 10),
    (4, 2, 4, 1, 30)
;

Answer 1

我认为您走在了正确的轨道上，但我决定重新开始并从头开始。为每个人获取 SplitValue 并不需要所有这些表。事实上，您只需要 sales_split 和 sales_person，就像这样:

SELECT sp.person_name, SUM(ss.split) AS SplitValue
FROM sales_person sp
JOIN sales_split ss ON sp.id = ss.sales_person_id
GROUP BY sp.id;

同样，您可以通过 sale、sales_split 和 sales_person 之间的联接获得每个人的总销售额:

SELECT sp.person_name, SUM(s.sale_value) AS SaleValue
FROM sale s
JOIN sales_split ss ON ss.sale_id = s.id
JOIN sales_person sp ON sp.id = ss.sales_person_id
GROUP BY sp.id;

此时，我意识到您的预期结果(对于此数据集)有误。事实上，Joe 的销售值(value)确实是 70，因为销售 ID 1(值(value) 20)、2(值(value) 20)和 4(值(value) 30)加起来是 70。但是，我仍然认为这个查询对您的帮助超过你拥有的那个。

此时，您可以通过将这两个子查询连接到 sales_person 表来获取每个 sales_person_id 的值。我在子查询中删除了对 sales_person 的连接，因为它现在变得无关紧要了。它甚至使子查询更简洁:

SELECT sp.person_name, COALESCE(t1.SplitValue, 0) AS SplitValue, COALESCE(t2.SaleValue, 0) AS SaleValue
FROM sales_person sp
LEFT JOIN(
  SELECT ss.sales_person_id, SUM(ss.split) AS SplitValue
  FROM sales_split ss
  GROUP BY ss.sales_person_id) t1 ON t1.sales_person_id = sp.id
LEFT JOIN(
  SELECT ss.sales_person_id, SUM(s.sale_value) AS SaleValue
  FROM sale s
  JOIN sales_split ss ON ss.sale_id = s.id
  GROUP BY ss.sales_person_id) t2 ON t2.sales_person_id = sp.id;

这是一个 SQL Fiddle示例。

---

编辑:我现在明白为什么 Joe 的实际销售价格是 50，因为他在销售 ID 1 上拆分了两次。为了解决这个问题，我首先得到了每个 sales_person 的不同销售列表，如下所示:

SELECT DISTINCT sale_id, sales_person_id
FROM sales_split;

这样，sales_person_id = 1 和 sale_id = 1 只有一行。然后，很容易将其加入 sale 表并获得每个 sales_person 的正确销售值:

SELECT t.sales_person_id, SUM(s.sale_value) AS SaleValue
FROM(
  SELECT DISTINCT sale_id, sales_person_id
  FROM sales_split) t
JOIN sale s ON s.id = t.sale_id
GROUP BY t.sales_person_id;

我上面的其余答案仍然适用。我编写了一个获取 SplitValue 的查询和一个获取 SaleValue 的查询，并将它们连接在一起。所以，我现在要做的就是用更远的不正确的子查询替换我刚刚给你的子查询:

SELECT sp.person_name, COALESCE(t1.SplitValue, 0) AS SplitValue, COALESCE(t2.SaleValue, 0) AS SaleValue
FROM sales_person sp
LEFT JOIN(
  SELECT ss.sales_person_id, SUM(ss.split) AS SplitValue
  FROM sales_split ss
  GROUP BY ss.sales_person_id) t1 ON t1.sales_person_id = sp.id
LEFT JOIN(
  SELECT t.sales_person_id, SUM(s.sale_value) AS SaleValue
  FROM(
    SELECT DISTINCT sale_id, sales_person_id
    FROM sales_split) t
  JOIN sale s ON s.id = t.sale_id
  GROUP BY t.sales_person_id) t2 ON t2.sales_person_id = sp.id;

这是更新的 SQL Fiddle .

您在评论中提到为简洁起见缩短了数据，这很好。我将保持连接不变，我相信它会为您提供足够的指导，以便您可以相应地调整它们以匹配您的正确结构。