34,571
社区成员
发帖
与我相关
我的任务
分享
declare @TableA table (
ID varchar(20),
signA int)
declare @TableB table (
ID varchar(20),
signB int)
Insert @TableA(ID, signA)
select '1001', 1
union all
select '1002', 2
union all
select '1003', 3
union all
select '1007', 4
Insert @TableB(ID, signB)
select '1001', 1
union all
select '1002', 2
union all
select '1003', 3
union all
select '1007', 4
Update a
Set a.signA = t.SignB
From
(Select m.ID, n.SignB
From (Select *, row_number() Over (Order by SignB) As SNo
From @TableB) m,
(Select *, row_number() Over (Order by SignB Desc) As SNo
From @TableB) n
Where m.SNo = n.SNo) t, @TableA a
Where t.ID = a.ID
Select * From @TableA
Order By signA
--> 测试数据:[tableA]
if object_id('[tableA]') is not null drop table [tableA]
create table [tableA]([ID] int,[signA] int)
go
insert [tableA]
select 1007,null union all
select 1003,null union all
select 1002,null union all
select 1001,null
--> 测试数据:[tableB]
if object_id('[tableB]') is not null drop table [tableB]
create table [tableB]([ID] int,[signB] int)
go
insert [tableB]
select 1001,1 union all
select 1002,2 union all
select 1003,3 union all
select 1007,4
update t
set [signA] = (select top 1 [signB] from (select t.id - id as id,[signB] from [tableB]
where id >= t.id) r
order by id desc)
from [tableA] t
select * from [tableA]
ID signA
----------- -----------
1007 4
1003 3
1002 2
1001 1
(4 行受影响)
多写了个top 100 percent
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-28 11:22:04
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] sql_variant,[signA] int)
insert [a]
select null,1 union all
select null,2 union all
select null,3 union all
select null,4
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[signB] int)
insert [b]
select 1001,1 union all
select 1002,2 union all
select 1003,3 union all
select 1007,4
--------------开始查询--------------------------
update
a
set
id=b.id
from
a,
(select id,signB=(select count(1)+1 from b where id>t.id) from b t )b
where
a.signa=b.signB
select * from a
----------------结果----------------------------
/* ID signA
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1007 1
1003 2
1002 3
1001 4
(4 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-28 11:22:04
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] sql_variant,[signA] int)
insert [a]
select null,1 union all
select null,2 union all
select null,3 union all
select null,4
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[signB] int)
insert [b]
select 1001,1 union all
select 1002,2 union all
select 1003,3 union all
select 1007,4
--------------开始查询--------------------------
update
a
set
id=b.id
from
a,
(select top 100 percent id,signB=(select count(1)+1 from b where id>t.id) from b t )b
where
a.signa=b.signB
select * from a
----------------结果----------------------------
/* ID signA
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1007 1
1003 2
1002 3
1001 4
(4 行受影响)
*/