34,875
社区成员
发帖
与我相关
我的任务
分享update 语句该如何写
各位好！
我有两张表 tableA ,tableB 其中都有id 字段关联，tableA中的signA字段根据tableB中的signB字段倒序排列更新。
例如 tableB表中三条记录
ID signB
1001 1
1002 2
1003 3
1007 4
则tableA 表的要求更新为
ID signA
1007 1
1003 2
1002 3
1001 4
实现 tableB表中signB字段 最大的数字，在tableA表中signA字段 为最小数字
我用的是sql server 2000
我有两张表 tableA ,tableB 其中都有id 字段关联，tableA中的signA字段根据tableB中的signB字段倒序排列更新。
例如 tableB表中三条记录
ID signB
1001 1
1002 2
1003 3
1007 4
则tableA 表的要求更新为
ID signA
1007 1
1003 2
1002 3
1001 4
实现 tableB表中signB字段 最大的数字，在tableA表中signA字段 为最小数字
我用的是sql server 2000
...全文
请发表友善的回复…
发表回复
好帅的一条鱼 2010-04-28
- 打赏
- 举报
学习00
yongnan_03 2010-04-28
- 打赏
- 举报
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
nianran520 2010-04-28
- 打赏
- 举报
错了。。
nianran520 2010-04-28
- 打赏
- 举报
--> 测试数据：[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 行受影响)
--小F-- 2010-04-28
- 打赏
- 举报
多写了个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 行受影响)
*/--小F-- 2010-04-28
- 打赏
- 举报
----------------------------------------------------------------
-- 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 行受影响)
*/
