update 语句该如何写

easyboot 2010-04-28 11:15:59
各位好!

我有两张表 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
...全文
119 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
好帅的一条鱼 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 行受影响)
*/

34,571

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧