SQL 添加包含来自联接的数据的新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8097384/
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
Adding a new column with data from a join
提问by dorianpc
What is the best approach to add a column to an existing table with values from a join...For example:
将列添加到具有连接值的现有表的最佳方法是什么...例如：
If I join Table A to Table B...
如果我将表 A 连接到表 B...
Select
A.Column1,
A.Column2,
B.Column1,
B.Column2,
B.Column3
FROM A
INNER JOIN B
ON A.Column1 = B.Column2
Basically I just want to copy the column that exists in Table B over to Table A, how can I add new A.Column3 to Table A to match B.Column3 based on the join?
基本上我只想将表 B 中存在的列复制到表 A，如何将新的 A.Column3 添加到表 A 以根据连接匹配 B.Column3？
回答by Steve Henderson
Try this:
尝试这个：
alter table A add column3 datatype
update A
set column3 = B.column3
from A inner join B on A.Column1 = B.Column2
回答by bpgergo
Note that this is probably notmost efficient method
请注意，这可能不是最有效的方法
alter table A add column3 [yourdatatype];
update A set column3 = (select column3 from B where A.Column1 = B.Column2)
where exists (select column3 from B where A.Column1 = B.Column2)
回答by aF.
First use an alter tablecommand to add the new column.
首先使用alter table命令添加新列。
Afterwords use updatecommand to put the values of B in column created in A.
后记使用更新命令将 B 的值放入 A 中创建的列中。
回答by Harshith Cariappa
You can do this:
你可以这样做：
alter A add column3 datatype;
update A
inner join (select column2 ,column3, count(*) as cnt from B group by column2) b
on A.column1 = b.column2 set
A.column3 = b.column3;
回答by Hamikzo
You can make a new table using INSERT statement:
您可以使用 INSERT 语句创建一个新表：
INSERT INTO
NEW_A
SELECT
A.*,
B.Column3
FROM A
INNER JOIN B
ON A.Column1 = B.Column2
