SQL 如何将两个表组合成拥有相同的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15038919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to combine two tables into own this the same columns?
提问by lkkeepmoving
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!
谢谢！
回答by John Woo
SELECT ID, SUM(Amount) total
FROM
(
SELECT ID, Amount FROM A
UNION ALL
SELECT ID, AMount FROM B
) s
GROUP BY ID
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;
回答by Rakesh Anand
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
