多个联接中的MySQL SUM函数 [英] MySQL SUM function in multiple joins

问题描述

这是我的情况，我有那些桌子

Hi so this is my case I have those tables

Customer {id,name}
Charges {id,amount,customer_id}
Taxes {id,amount,charge_id}

所以我想总计费用和税款，然后按客户ID分组，这是我的查询

so I want to SUM amount of charges and taxes then group by customer id here is my query

SELECT SUM(ch.amount),SUM(t.amount)
FROM Customer c
LEFT JOIN Charges ch ON ch.customer_id = c.id
LEFT JOIN Taxes t ON t.charge_id = ch.id
GROUP BY c.id;

因此，如果我对客户收取1笔费用，而当我使用SUM函数时要为该笔费用收取2笔税，则它会对费用金额进行两次计数，例如，如果要向我显示10美元，则向我显示20美元

so in case I have 1 charge for customer than I have 2 taxes for that charge when I use SUM function it's counting amount of charge twice for example in case to show me 10$ it' showing me 20$

我知道如何通过子查询解决此问题，但是我想知道是否有任何选项可以在没有子查询之类的情况下获得正确的值，例如上面使用的查询，我可以在那里进行修改以解决该问题.

I know how can I fix that through subqueries, but I want to know is there any option to get correct value without subqueries like query I use above what can I modify there to fix that.

谢谢！

没有子查询的更新答案

SELECT
  SUM(CASE WHEN @ch_id != ch.id
    THEN ch.amount END) AS ch_amount,
  SUM(t.amount)         AS t_sum,
  c.*,
  @ch_id := ch.id
FROM
  Customer c
  LEFT JOIN charges ch ON c.id = ch.reservation_id
  LEFT JOIN taxes t ON ch.id = t.charge_id
GROUP BY rs.id;

推荐答案

您想知道是否可以在没有子查询的情况下执行此操作. 不，您不能.

You want to know if you can do this without subqueries. No, you can't.

如果收费"中的一行在税费"中具有多个对应行，那么您不能简单地联接表而不复制收费"行.然后，如您所知，对它们进行汇总时，将获得多份副本.

If a row in Charges has more than one corresponding row in Taxes, you can't simply join the tables without duplicating Charges rows. Then, as you have discovered, when you sum them up, you'll get multiple copies.

您需要一种获取虚拟表(子查询)的方法，该虚拟表的每个Charge都包含一行.

You need a way to get a virtual table (a subquery) with one row for each Charge.

                    SELECT ch.customer_id,
                           ch.amount amount,
                           tx.tax tax
                      FROM Charges
                      LEFT JOIN (  
                                  SELECT SUM(amount) tax,
                                         charge_id 
                                    FROM Taxes
                                   GROUP BY charge_id
                          ) tx ON ch.id = tx.charge_id

然后，您可以将该子查询加入到Customer表中，以按客户汇总销售额.

You can then join that subquery to your Customer table to summarize sales by customer.

这篇关于多个联接中的MySQL SUM函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！