例如:Table1
-----------------
F1
d1
d2
d3
d4
d5Table2
-----------------
F1 F2
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL希望得到如下结果:
Table2
-----------------
F1 F2
1 d1
2 d2
3 d3
4 d4
5 d5
-----------------
F1
d1
d2
d3
d4
d5Table2
-----------------
F1 F2
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL希望得到如下结果:
Table2
-----------------
F1 F2
1 d1
2 d2
3 d3
4 d4
5 d5
from Table2 a,(select row_number() over(order by F1) no,* from Table1) b
where a.F1=b.no
select f1,row_number()over(order by (select 1))rn from table1
)b on a.f1=b.rn
b
set
f2=a.f1
from
(select row_number() over(order by F1) as px,* from Table1)a
left join
table2
on
a.px=b.no
declare @Table1 table (F1 varchar(2))
insert into @Table1
select 'd1' union all
select 'd2' union all
select 'd3' union all
select 'd4' union all
select 'd5'declare @Table2 table (F1 int,F2 VARCHAR(10))
insert into @Table2
select 1,null union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,NULL;WITH maco AS(SELECT ID=ROW_NUMBER() OVER (ORDER BY GETDATE()), * FROM @Table1)UPDATE @Table2
SET F2=b.F1
FROM @Table2 a LEFT JOIN maco b ON a.F1=b.IDselect * from @Table2
/*
F1 F2
----------- ----------
1 d1
2 d2
3 d3
4 d4
5 d5
*/
create table Table1(F1 char(2))insert into Table1
select 'd1' union all
select 'd2' union all
select 'd3' union all
select 'd4' union all
select 'd5'create table Table2(F1 int, F2 char(2))insert into Table2
select 1, NULL union all
select 2, NULL union all
select 3, NULL union all
select 4, NULL union all
select 5, NULL
select * from Table2F1 F2
----------- ----
1 NULL
2 NULL
3 NULL
4 NULL
5 NULLupdate b
set b.F2=a.F1
from (select row_number() over(order by (select 0)) rn,F1 from Table1) a
inner join Table2 b
on a.rn=b.F1select * from Table2F1 F2
----------- ----
1 d1
2 d2
3 d3
4 d4
5 d5
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([F1] nvarchar(20))
Insert #T
select N'd1' union all
select N'd2' union all
select N'd3' union all
select N'd4' union all
select N'd5'
Go
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([F1] int,[F2] NVARCHAR(20))
Insert #T2
select 1,null union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,null
GoALTER TABLE #T ADD ID INT IDENTITY
GO
UPDATE b
SET F2=a.[F1]
FROM #T AS a INNER JOIN #T2 AS b ON a.ID=b.F1
GO
ALTER TABLE #T DROP COLUMN ID
set b.F2=a.F1
from Table1 a
inner join Table2 b
on right(a.F1,1)=cast(b.F1 as char(1))select * from Table2F1 F2
----------- ----
1 d1
2 d2
3 d3
4 d4
5 d5
update B set F2=A.F1 from
Table1 A,Table2 B where right(A.F1,1)=B.F1
-----------
F1
T1_1
T1_2
T1_3Table2
-----------
F1
T2_1
T2_2
T3_3把Table1和Table2的某一列,更新至Table3的两列中,有没有简单的办法?Table3
-----------
F1 F2
T1_1 T2_1
T1_2 T2_2
T1_3 T2_3
update B set B.F2=A.F1 from
Table1 A,Table2 B where right(A.F1,1)=B.F1
table1和table2,table3都加个自增列 然后关联就行了
create table Table1
(
F1 varchar(10)
)create table Table3
(
F1 varchar(10),
F2 varchar(10)
)create table Table2
(
F1 varchar(10)
)insert into Table1
select 'T1_1' union all
select 'T1_2' union all
select 'T1_3'
insert into Table2
select 'T2_1' union all
select 'T2_2' union all
select 'T2_3'insert into Table3
select a.F1,b.F1 from Table1 a,Table2 b where right(a.F1,1)=right(b.F1,1)
create table Table1(F1 char(5))insert into Table1
select 'T1_1' union all
select 'T1_2' union all
select 'T1_3'create table Table2(F1 char(5))insert into Table2
select 'T2_1' union all
select 'T2_2' union all
select 'T2_3'
select identity(int,1,1) rn,* into #t1 from Table1
select identity(int,1,1) rn,* into #t2 from Table2select a.F1 'F1',b.F1 'F2'
into Table3
from #t1 a
inner join #t2 b
on a.rn=b.rnselect * from Table3F1 F2
----- -----
T1_1 T2_1
T1_2 T2_2
T1_3 T2_3