22,199
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-28 16:30:39
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(5),[col2] int)
insert [tb]
select '00002',1 union all
select '00002',2 union all
select '00002',3 union all
select '00003',1 union all
select '00003',2 union all
select '00003',3
--------------开始查询--------------------------
update
b
set
col1='00004',col2=10
from
tb a,(select id=row_number()over(partition by col1 order by getdate()),* from tb )b
where
a.col1=b.col1
and
b.id=1
select * from tb
----------------结果----------------------------
/* col1 col2
----- -----------
00004 10
00002 2
00002 3
00004 10
00003 2
00003 3
(6 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-28 16:30:39
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(5),[col2] int)
insert [tb]
select '00002',1 union all
select '00002',2 union all
select '00002',3 union all
select '00003',1 union all
select '00003',2 union all
select '00003',3
--------------开始查询--------------------------
update
b
set
col1='00004',col2=10
from
tb a,(select id=row_number()over(partition by col1 order by getdate()),* from tb where col2=1)b
where
a.col1=b.col1
and
b.id=1
select * from tb
----------------结果----------------------------
/* col1 col2
----- -----------
00004 10
00002 2
00002 3
00004 10
00003 2
00003 3
(6 行受影响)
*/
update tb
set ...
from tb t where val = (select min(val) from tb where id = t.id)
update tb
set ...
from tb t where not exists (select 1 from tb where id = t.id and val < t.val)
update tb
set ...
from tb t where val = (select top 1 val from tb where id = t.id order by val)
update
tb
set
col1=t.col1,col2=t.col2
from
tb a,(select id=row_number()over(paitition by col1 order by getdate())b
where
a.col1=b.col
and
b.id=1
update tb
set ...
where ... --条件控制就可以!
--楼主给出你的测试数据和结果,这样看不出来什么。