如何复制一些列并同时更改一列?
问题描述:
我想复制第三列和第四列,但更改第二列。我该怎么办?我想知道如何编写sql查询。谢谢。如何复制一些列并同时更改一列?
例如:
表1:
1, 1, aaa, bbb
2, 1, ads, bff
3, 1, awq, bcc
,我想table1的最后是:
1, 1, aaa, bbb
2, 1, ads, bff
3, 1, awq, bcc
4, 2, aaa, bbb
5, 2, ads, bff
6, 2, awq, bcc
(第一列是ID)
答
这应该工作:
insert into table1 (column2,column3,column4)
select 2,column3, column4
from table1 where column2 = 1
+0
谢谢!有用。 –
+0
@DanniChen不用客气 – Jens
答
你的建议:
select (@cnt:[email protected] + 1) as RowNumber,a.c1,a.c2,a.c3 from
(select 1 as id, 1 as c1, 'aaa' as c2, 'bbb'as c3 union all
select 2, 1, 'ads', 'bff' union all
select 3, 1, 'awq', 'bcc'
union all
select 1 as id, 1 as c1, 'aaa' as c2, 'bbb'as c3 union all
select 2, 1, 'ads', 'bff' union all
select 3, 1, 'awq', 'bcc') as a
cross JOIN
(select @cnt:=0) as tmp
结果:
1 1 aaa bbb
2 1 ads bff
3 1 awq bcc
4 1 aaa bbb
5 1 ads bff
6 1 awq bcc
,问题是什么? – Jens
请添加一些代码,显示您已经尝试过的内容。 – EFrank
我想复制第三列和第四列,但更改第二列。我该怎么办? –