如何将两个表组合成自己的相同列？

I have two tables A and B. A has two columns: id, amount. B also has two columns: id, amount. I hope to combine A and B to create a new table C, with same two columns:id, amount. How can I do it using SQL? For example:

我有两个表A和B. A有两列：id，amount。 B还有两列：id，amount。我希望结合A和B来创建一个新的表C，具有相同的两列：id，amount。我怎么能用SQL做到这一点？例如：

A
    ('A1',1)
    ('A2',5)
    ('A3',2)
    ('A4',5)
    ('A5',2)
    ('A6',7)
B
    ('A1',3)
    ('A3',2)
    ('A4',7)
    ('A5',4)
    ('A8',2)
    ('A9',10)

so C should be:

所以C应该是：

C
    ('A1',4)
    ('A2',5)
    ('A3',4)
    ('A4',12)
    ('A5',6)
    ('A6',7)
    ('A8',2)
    ('A9',10)

Thank you!

谢谢！

2 个解决方案

#1

SELECT  ID, SUM(Amount) total
FROM
        (
            SELECT ID, Amount FROM A
            UNION ALL
            SELECT ID, AMount FROM B
        ) s
GROUP   BY ID

SQLFiddle Demo
SQLFiddle演示

You can create a table base on the result from the query.

您可以根据查询的结果创建表。

CREATE TABLE C
AS
SELECT  ID, SUM(Amount) total
FROM
        (
            SELECT ID, Amount FROM A
            UNION ALL
            SELECT ID, AMount FROM B
        ) s
GROUP   BY ID;

SQLFiddle Demo
SQLFiddle演示

#2

the answer above works absolutely fine. Just to add to it an order by clause that will sort by ID.

上面的答案非常好。只是添加一个order by子句，它将按ID排序。

SELECT  ID, SUM(Amount) as total
FROM
        (
            SELECT ID, Amount FROM A
            UNION ALL
            SELECT ID, AMount FROM B
        ) s
GROUP by ID
order by ID

#1

SELECT  ID, SUM(Amount) total
FROM
        (
            SELECT ID, Amount FROM A
            UNION ALL
            SELECT ID, AMount FROM B
        ) s
GROUP   BY ID

SQLFiddle Demo
SQLFiddle演示