使用内部联接组合mysql查询SUM()结果 [英] combining mysql query SUM() result using inner join

问题描述

我有这两个表

table1 : date | uid | value_in
table2 : date | uid | value_out 

每天，无论交易/记录的数量如何，每个uid都将收到一个进出余额的值

each day, every uid will receive a balance value for both in and out, regardless of the number of transaction/records

我要做的是将查询结果合并为这样的

what I want to do is to combine the query result to be like this

date | uid | value_in | value_out | (value_in-value_out) as balance

但是，当我执行此查询时

however, when I do this query

SELECT 
  a.date,
  a.uid,
  SUM(a.value_in),
  SUM(b.value_out),
  (SUM(a.value_in)-SUM(b.value_out)) AS balance
FROM table1 a
INNER JOIN table2 b ON a.date=b.date AND a.uid=b.uid
GROUP BY a.date, a.uid

它产生无效的结果(SUM是两倍或三倍) 我应该如何修改查询，使查询结果不会翻倍?

it produce invalid result (the SUM is doubled or tripled) how should I modify my query so it does not produce doubled result ?

推荐答案

首先建立总和，然后加入.像这样:

First build the sum, then join. Like this:

SELECT i.uid
      ,i.date
      ,i.v_in
      ,COALESCE(o.v_out, 0) AS v_out
      ,(i.v_in - COALESCE(o.v_out, 0)) AS balance
FROM (
    SELECT date
          ,uid
          ,SUM(value_in) AS v_in
    FROM   table1
    GROUP  BY 1,2
    ) i
LEFT JOIN (
    SELECT date
          ,uid
          ,SUM(value_out) AS v_out
    FROM   table2
    GROUP  BY 1,2
    ) o USING (date, uid)

您拥有它的方式，每个value_in将与每个匹配的value_out组合在一起，从而将数字相乘.您必须先进行汇总，然后再加入一个和一个的总和，然后一切都会变得很糟.是吗?

The way you had it, every value_in would be combined with every matching value_out, thereby multiplying the numbers. You must aggregate first and then you join one in-sum with one out-sum and everything is groovy. Or is it?

如果给定的(date, uid)没有value_in或value_out，会发生什么?或仅value_in或仅value_out?您的查询将失败.

What happens if there are no value_in or value_out for a given (date, uid)? Or only value_in Or just value_out? Your query will fail.

通过使用LEFT JOIN而不是[INNER] JOIN进行了改进，但是您真正想要的是FULL OUTER JOIN-MySQL中缺少的功能之一.

I improved by using a LEFT JOIN instead of [INNER] JOIN, but what you really want is a FULL OUTER JOIN - one of the missing features in MySQL.

您可以在单独的表中提供日期列表，并在两个表中都提供LEFT JOIN，也可以使用两次LEFT JOIN和UNION来解决缺少的功能. 参见此处例子.

You can either provide a list of days in a separate table and LEFT JOIN both tables to it, or you can work around the missing feature with two times LEFT JOIN and UNION. See here for an example.

或者，您可以将value_out乘以-1两个表的UNION并建立一个和.

Or you could multiply your value_out by -1 UNION both tables together and build one sum.

但是，您仍然，如果没有任何value_in或value_out，这将导致您连续一天无法通过，这违反了您的描述.

But you still would not get a row for a day without any value_in or value_out, which violates your description.

因此，唯一干净的解决方案是要在表中包含所需的所有(date, uid)，并在其中包含LEFT JOIN table1和table2的总和，或UNION ALL子选择中的三个(负table2)，然后求和.

So, the only clean solution is to have a table with all (date, uid) you want in the result and LEFT JOIN the sums of table1 and table2 to it, or UNION ALL the three (negative table2) in a sub-select and then sum up.

这篇关于使用内部联接组合mysql查询SUM()结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！