mysql - 来自两个不同表的MYSQL总和

我有两张桌子。
我需要将这两个表的每一行合并为table3中的一行。我设法获得了table1 SUM的金额，但没有获得table2。

例如。

表用户

 +---------+-----------+
 | user_id | user_name |
 +---------+-----------+
 | 001     |   JOHN    |
 | 002     |   ADAM    |
 +---------+-----------+

表格1

+-----------+----------------+-------------------+---------------------+
| table1_id | table1_user_id |    table1_amount  |       table1_date   |
+-----------+----------------+-------------------+---------------------+
|  6        | 001            |    100            |  01/11/2014 10:55   |
|  7        | 002            |   100             |  01/11/2014 10:55   |
|  8        | 001            |    50             |  25/10/2014 10:55   |
|  9        | 001            |   100             |  23/10/2014 11:00   |
|  10       | 002            |    0              |  21/10/2014 11:00   |
+-----------+----------------+-------------------+---------------------+

表2

 +-----------+----------------+----------------+--------------------+
 | table2_id | table2_user_id |  table2_amount |  table2_date       |
 +-----------+----------------+----------------+--------------------+
 |   1       |    001         |   100          |   15/11/2014 10:55 |
 |   2       |    001         |   100          |   15/10/2014 10:55 |
 |   3       |    002         |   100          |   11/10/2014 10:55 |
 |   4       |    001         |   50           |   11/10/2014 10:55 |
 +-----------+----------------+----------------+--------------------+

预期结果：

表3

+-----+---------+---------------+---------------+----------+---------+
| id  | user_id | table1_amount | table2_amount |    Year  |  Month  |
+-----+---------+---------------+---------------+----------+---------+
| 1   |  001    |    100        |       100     |     2014 |    11   |
| 2   |  002    |    100        |        0      |     2014 |    11   |
| 3   |  001    |    150        |       150     |     2014 |    10   |
| 4   |  002    |     0         |       100     |     2014 |    10   |
+-----+---------+---------------+---------------+----------+---------+

我的尝试，但未显示出预期的结果。每行中table2_amount的数量为NULL：

SQL=" INSERT INTO table3
      SELECT user_id,SUM(table1_amount),t2.amount2,
      YEAR(table1_date),MONTH(table1_date) FROM table1 a
      LEFT JOIN
     (SELECT c.table2_user_id,SUM(c.table2_amount) as amount2,c.table2_date
              FROM   table2 c
       GROUP BY DATE_FORMAT(c.table2_date,'%Y-%m'),c.table2_user_id ASC
              ) t2
     on t2.table2_user_id = a.table1_user_id AND t2.table2_date = a.table1_date
     GROUP BY DATE_FORMAT(a.table1_date,'%Y-%m'),table1_user_id ASC ";
"

最佳答案

对于UNION来说，这是一个不错的任务

SELECT tx.uid,SUM(tx.a1),SUM(tx.a2),YEAR(tx.d),MONTH(tx.d)
FROM
(
SELECT t1.table1_user_id as uid,
  t1.table1_amount as a1,
  0 as a2,
  t1.table1_date as d
  FROM table1 t1
UNION
SELECT t2.table2_user_id as uid,
  0 as a1,
  t2.table2_amount as a2,
  t2.table2_date as d
  FROM table2 t2
) tx
GROUP BY DATE_FORMAT(d,'%Y-%m'),uid ASC

关于mysql - 来自两个不同表的MYSQL总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26775974/