mysql 多对一和分组给出不寻常的结果

Question

我在以下相当简单的设置中遇到困难：

CREATE TABLE IF NOT EXISTS invoices (
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS invoices_items (
id int(11) NOT NULL auto_increment,
invoice_id int(11) NOT NULL,
description text NOT NULL,
amount decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS invoices_payments (
id int(11) NOT NULL auto_increment,
invoice_id int(11) NOT NULL,
amount decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (id)
);

一些数据：

INSERT INTO invoices (id) VALUES (1);

INSERT INTO invoices_items (id, invoice_id, description, amount) VALUES
(1, 1, 'Item 1', '750.00'),
(2, 1, 'Item 2', '750.00'),
(3, 1, 'Item 3', '50.00'),
(4, 1, 'Item 4', '150.00');

INSERT INTO invoices_payments (id, invoice_id, amount) VALUES
(1, 1, '50.00'),
(2, 1, '1650.00');

并且sql产生不寻常的结果：

select  invoices.id,
        ifnull(sum(invoices_payments.amount),0) as payments_total, 
        ifnull(count(invoices_items.id),0) as item_count 
from    invoices
            left join invoices_items on invoices_items.invoice_id=invoices.id 
            left join invoices_payments on invoices_payments.invoice_id=invoices.id 
group by invoices.id

现在导致（错误）输出

id  payments_total  item_count
1   6800.00     8

，事实证明只有四个“invoice_item”行，我不明白为什么mysql不分组适当地。

编辑

我知道我可以做这样的事情：

select x.*, ifnull(sum(invoices_payments.amount),0) as payments_total from (
    select  invoices.id,
            ifnull(count(invoices_items.id),0) as item_count 
    from    invoices
                left join invoices_items on invoices_items.invoice_id=invoices.id 
    group by invoices.id
) as x left join invoices_payments on invoices_payments.invoice_id=x.id
group by x.id

但我想知道我在第一个查询中是否做错了什么 - 我无法立即明白为什么第一个查询给出了错误的结果！ :(

Answer 1

您的加入逻辑不正确。在您的联接中，您指定发票_项目.发票_id = 发票.id。您还可以指定发票_付款.发票_id = 发票.id。由于传递性，您最终得到：

invoices_items.invoice_id    = invoices.id
invoices_payments.invoice_id = invoices.id
invoice_items.invoice_id     = invoices_payments.invoice_id

2 份发票付款的总和为 1700 美元。对于每笔发票付款，有4个invoice_items满足上述关系。 1700 美元 * 4 = 6800 美元。

对于每个发票项目，都会有两个满足上述关系的发票付款。 4 个发票项目 * 2 = 8 个。

Answer 2

有两个表与发票具有多：一关系。您的计数是笛卡尔积。

付款应应用于发票，而不是发票项目。首先获取发票总额，然后将付款加入其中。

这可能与您正在寻找的内容类似：

SELECT
    invoice_total.invoice_id,
    invoice_total.amount as invoice_amount,
    payments_total.amount as total_paid
FROM
    (
        SELECT
            invoice_id,
            SUM(amount) as amount
        FROM
            invoices_items
        GROUP BY
                invoice_id
        ) invoice_total
    INNER JOIN
            (
            SELECT 
                invoice_id,
                SUM(amount) as amount
            FROM
                invoices_payments
            GROUP BY
                invoice_id
            ) payments_total
    ON invoice_total.invoice_id  = payments_total.invoice_id;

Answer 3

编辑：

啊，抱歉 - 现在看看你的观点。您得到意外结果的原因是此查询：

SELECT * 
FROM invoices
    LEFT JOIN invoices_items ON invoices_items.invoice_id = invoices.id
    LEFT JOIN invoices_payments ON invoices_payments.invoice_id = invoices.id;

导致：

id  id invoice_id   description amount  id  invoice_id  amount
1    1  1            Item 1      750.00  1   1           50.00
1    1  1            Item 1      750.00  2   1           1650.00
1    2  1            Item 2      750.00  1   1           50.00
1    2  1            Item 2      750.00  2   1           1650.00
1    3  1            Item 3      50.00   1   1           50.00
1    3  1            Item 3      50.00   2   1           1650.00
1    4  1            Item 4      150.00  1   1           50.00
1    4  1            Item 4      150.00  2   1           1650.00

如您所见，您为每个 invoices_ payments 记录获取每个 invoices_items 记录一次。您将必须分别抓取（即分组）它们。

请注意，初始查询中的 GROUP BY 子句是多余的。

这是您需要的：

SELECT
    invoices.id,
    payments_total.payments_total, 
    IFNULL(COUNT(invoices_items.id),0) AS item_count 
FROM invoices
    LEFT JOIN invoices_items ON invoices.id = invoices_items.invoice_id
    LEFT JOIN (
        SELECT invoice_id,
            IFNULL(SUM(invoices_payments.amount),0) AS payments_total
            FROM invoices_payments
            GROUP BY invoice_id
        ) AS payments_total ON invoices.id  = payments_total.invoice_id
;