有两个表
tab1 DAH
123
234
567
tab2
SYSID
YH00000001
YH00000002
YH00000003 我想更新成这样子 SYSID DAH
YH00000001 123
YH00000002 234
YH00000003 567
2个表合并起来....
tab1 DAH
123
234
567
tab2
SYSID
YH00000001
YH00000002
YH00000003 我想更新成这样子 SYSID DAH
YH00000001 123
YH00000002 234
YH00000003 567
2个表合并起来....
SELECT IDD=IDENTITY(INT,1,1),* INTO #T1 FROM TAB2
SELECT T1.SYSID,T.DAH FROM #T,#T1 WHERE T.IDD=T1.IDD
if object_id('[ta]') is not null drop table [ta]
create table [ta]([DAH] int)
insert [ta]
select 123 union all
select 234 union all
select 567select * from [ta]
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([SYSID] varchar(10))
insert [tb]
select 'YH00000001' union all
select 'YH00000002' union all
select 'YH00000003'
-----------------------
select b.SYSID,a.DAH from
(select [DAH] ,id=(row_number () over (order by [DAH]))from ta)a
join
(select [SYSID] ,id=(row_number () over (order by [SYSID]))from tb) b
on a.id=b.id/*
DAH SYSID
----------- ----------
123 YH00000001
234 YH00000002
567 YH00000003(3 行受影响)
*/
select b.SYSID,a.DAH from
(select [DAH] ,id=(row_number () over (order by [DAH]))from ta)a
join
(select [SYSID] ,id=(row_number () over (order by [SYSID]))from tb) b
on a.id=b.id--2000
select b.SYSID,a.DAH from
(select [DAH] ,id=(select count(1) from ta where [DAH]<=t.[DAH] )from ta as t )a
join
(select [SYSID] ,id=(select count(1) from tb where [SYSID]<=t.[SYSID] )from tb as t) b
on a.id=b.id
'row_number' 不是可以识别的 函数名。
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([DAH] int)
insert [ta]
select 123 union all
select 234 union all
select 567--select * from [ta]
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([SYSID] varchar(10))
insert [tb]
select 'YH00000001' union all
select 'YH00000002' union all
select 'YH00000003'
--alter table tb add dah int
SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM Taupdate tb
set dah=t.dah
from #t t
WHERE cast(right(sysid,1)as int)=t.IDDselect * from tbSYSID dah
---------- -----------
YH00000001 123
YH00000002 234
YH00000003 567(3 行受影响)drop table #t
select id=identity(int,1,1),* into #t2 from [tb]select #t1.[DAH],#t2.[SYSID] from #t1,#t2 where #t1.id=#t2.id