时间戳

首页 > 解决方案 > 如何对两个表的值求和并按日期分组

mysql - 如何对两个表的值求和并按日期分组

问题描述

我正在构建一个交易系统，用户需要按日期了解特定用户 (uid) 的流动账户余额，包括他们从交易中赚了多少钱（结果表）以及他们从账户中存入或提取了多少（调整表）。

这是 sqlfiddle 和表格：http ://sqlfiddle.com/#!9/6bc9e4/1 

Adjustments table:

+-------+-----+-----+--------+------------+
| adjid | aid | uid | amount |    date    |
+-------+-----+-----+--------+------------+
|     1 |   1 |   1 |     20 | 2019-08-18 |
|     2 |   1 |   1 |     50 | 2019-08-21 |
|     3 |   1 |   1 |     40 | 2019-08-21 |
|     4 |   1 |   1 |     10 | 2019-08-19 |
+-------+-----+-----+--------+------------+

Results table:

+-----+-----+-----+--------+-------+------------+
| tid | uid | aid | amount | taxes |    date    |
+-----+-----+-----+--------+-------+------------+
|   1 |   1 |   1 |    100 |     3 | 2019-08-19 |
|   2 |   1 |   1 |    -50 |     1 | 2019-08-20 |
|   3 |   1 |   1 |    100 |     2 | 2019-08-21 |
|   4 |   1 |   1 |    100 |     2 | 2019-08-21 |
+-----+-----+-----+--------+-------+------------+

如何获得 uid (1) 的以下结果

+--------------+------------+------------------+----------------+------------+
| ResultsTotal | TaxesTotal | AdjustmentsTotal | RunningBalance |    Date    |
+--------------+------------+------------------+----------------+------------+
| -            | -          | 20               |             20 | 2019-08-18 |
| 100          | 3          | 10               |            133 | 2019-08-19 |
| -50          | 1          | -                |             84 | 2019-08-20 |
| 200          | 4          | 90               |            378 | 2019-08-21 |
+--------------+------------+------------------+----------------+------------+

其中 RunningBalance 是特定用户 (uid) 的当前帐户余额。

根据@Gabriel的回答，我想出了类似的东西，但它给了我空余的余额和重复的记录

SELECT SUM(ResultsTotal), SUM(TaxesTotal), SUM(AdjustmentsTotal), @runningtotal:= @runningtotal+SUM(ResultsTotal)+SUM(TaxesTotal)+SUM(AdjustmentsTotal) as Balance, date
FROM (
  SELECT 0 AS ResultsTotal, 0 AS TaxesTotal, adjustments.amount AS AdjustmentsTotal, adjustments.date
  FROM adjustments LEFT JOIN results ON (results.uid=adjustments.uid) WHERE adjustments.uid='1'
  UNION ALL
  SELECT results.amount AS ResultsTotal, taxes AS TaxesTotal, 0 as AdjustmentsTotal, results.date
  FROM results LEFT JOIN adjustments ON (results.uid=adjustments.uid) WHERE results.uid='1'
) unionTable
GROUP BY DATE ORDER BY date

标签： mysql

解决方案

对于您要询问的内容，您希望合并然后对两个表中的结果进行分组，这应该会给出您想要的结果。但是，我建议在 MySQL 之外计算运行余额，因为这会给我们的查询增加一些复杂性。

奇怪的事情可能会开始发生，例如，如果有人已经将 @runningBalance 变量定义为查询范围的一部分。

SELECT aggregateTable.*, @runningBalance := ifNULL(@runningBalance, 0) + TOTAL
FROM (
  SELECT SUM(ResultsTotal), SUM(TaxesTotal), SUM(AdjustmentsTotal)
   , SUM(ResultsTotal) + SUM(TaxesTotal) + SUM(AdjustmentsTotal) as TOTAL
   , date
  FROM (
    SELECT 0 AS ResultsTotal, 0 AS TaxesTotal, amount AS AdjustmentsTotal, date 
    FROM adjustments
    UNION ALL
    SELECT amount AS ResultsTotal, taxes AS TaxesTotal, 0 as AdjustmentsTotal, date
    FROM results
  ) unionTable
  GROUP BY date
) aggregateTable

推荐阅读