mysql - SQL按名称对相似表进行分组

Question

我有两个相似的表

表一

  | id | name | amount|
  | 2  | Mike | 1000  |
  | 3  | Dave | 2500  |

表2

  | id | name | amount|
  | 2  | Mike | 1200  |
  | 4  | James| 2500  |

我想查询表以获得这样的结果:

  | id | name | amount_table1| amount_table2|
  | 2  | Mike | 1000         | 1200         |
  | 3  | Dave | 2500         |              |
  | 4  | james|              | 2500         |

Answer 1

UNION ALL 表。执行 GROUP BY 以获得每个 id/name 组合一行。

select id, name, sum(amount1), sum(amount2)
from
(
    select id, name, amount as amount1, null as amount2 from table1
    union all
    select id, name, null, amount  from table2
) dt
group by id, name