update 语句该如何写

Question

本帖最后由 easyboot 于 2010-04-28 11:17:29 编辑

Answer 1

----------------------------------------------------------------
-- 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.signBselect * from a
----------------结果----------------------------
/* ID                                                                                                                                                                                                                                                               signA
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1007                                                                                                                                                                                                                                                             1
1003                                                                                                                                                                                                                                                             2
1002                                                                                                                                                                                                                                                             3
1001                                                                                                                                                                                                                                                             4(4 行受影响)
*/

Answer 2

多写了个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.signBselect * from a
----------------结果----------------------------
/* ID                                                                                                                                                                                                                                                               signA
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1007                                                                                                                                                                                                                                                             1
1003                                                                                                                                                                                                                                                             2
1002                                                                                                                                                                                                                                                             3
1001                                                                                                                                                                                                                                                             4(4 行受影响)
*/

Answer 3

--> 测试数据：[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,4update 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 行受影响)

Answer 4

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', 4Insert @TableB(ID, signB)
select '1001', 1
union all
select '1002', 2
union all
select '1003', 3
union all
select '1007', 4Update 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.IDSelect * From @TableA
Order By signA