SQL 2个求和给予了不正确的结果

问题描述：

Im做一个查询在那里我做2个求和，但它发出了一种怪异的结果SQL 2个求和给予了不正确的结果

SELECT 
    SUM(Details.price * Details.quantity) AS total_amount, 
    SUM(Payment.amount_paid) as paid_amount 
FROM 
    Details, 
    Invoice 
LEFT JOIN 
    Payment ON Invoice.id = Payment.fk_invoice_id 
WHERE 
    AND Invoice.id = 1 
    AND Invoice.id = Details.fk_invoice_id 
GROUP BY Details.fk_invoice_id;

什么是目前发生的事情是可以说有在支付3行相匹配的选择标准并且只有1个细节，total_amount中的总和乘以3，但是paid_amount是正确的。然后让我们说明细节中有2项与条件相符，付款方式为1，paid_amount乘以2，但total_amount是正确的。任何提示都会很棒，谢谢！

架构

Invoice 
    id 
    Name.... 


Details 
    id 
    fk_invoice_id 
    name 
    price 
    quantity 

Payment 
    id 
    fk_invoice_id 
    amount_paid

更多细节：

我正在努力简化我的职位，但只是添加

客户有许多发票

我只能是一个参考给客户，所以customer.id是发票中的外键，即时尝试获取特定客户的所有发票。

答

所有你想要做的就是从细节和东西从发票付款的东西。但你正在加入所有相关记录。所以说，有3个详细记录和发票，那么你乘以2的详细结果2条的缴费记录，并通过3.付款结果您可以鸿沟：

SELECT 
    SUM(Details.price * Details.quantity)/GREATEST(COUNT(DISTINCT Payment.id),1) AS total_amount, 
    SUM(Payment.amount_paid)/COUNT(DISTINCT Details.id) as paid_amount 
FROM Details 
JOIN Invoice ON Invoice.id = Details.fk_invoice_id AND Invoice.id = 1 
LEFT JOIN Payment ON Invoice.id = Payment.fk_invoice_id 
GROUP BY Invoice.id;

或者干脆不参加什么切不可加入。这里有一种方法：

SELECT 
    (SELECT SUM(Details.price * Details.quantity) FROM Details WHERE Details.fk_invoice_id = Invoice.id) AS total_amount, 
    (SELECT SUM(Payment.amount_paid) FROM Payment WHERE Payment.fk_invoice_id = Invoice.id) AS paid_amount 
FROM Invoice 
WHERE id = 1;

这里是另一个，其中前加入聚合完成。（我们不需要发票表无论如何，顺便说一句，我在这里将其删除。）

SELECT 
    d.total_amount, 
    p.paid_amount 
FROM 
(
    SELECT fk_invoice_id, SUM(price * quantity) as total_amount 
    FROM Details 
    GROUP BY fk_invoice_id 
) d ON d.fk_invoice_id = Invoice.id 
LEFT JOIN 
(
    SELECT fk_invoice_id, SUM(Payment.amount_paid) as paid_amount 
    FROM Payment 
    GROUP BY fk_invoice_id 
) p ON p.fk_invoice_id = Invoice.id 
WHERE d.fk_invoice_id = 1;

只要你只是寻找一个发票号，所有上述语句可以simplyfied当然。例如：

SELECT 
    (SELECT SUM(Details.price * Details.quantity) FROM Details WHERE Details.fk_invoice_id = 1) AS total_amount, 
    (SELECT SUM(Payment.amount_paid) FROM Payment WHERE Payment.fk_invoice_id = 1) AS paid_amount;

SQL 2个求和给予了不正确的结果

相关推荐