＋设为首页 ＋添加到收藏夹
有下表：
No Name Value1 Value2 Sign
123 aaaa 12 10
123 bbbb 12 11
123 cccc 12 9
456 aaaa 15 10
456 bbbb 15 12
456 cccc 15 10
按 No 分组，取 Value1-Value2 为最大值的行，Update Sing为1
根据 No,Name可以确定唯一行，即 No,Name 为主键
得到结果如下：
No Name Value1 Value2 Sign
123 aaaa 12 10
123 bbbb 12 11
123 cccc 12 9 1
456 aaaa 15 10 1
456 bbbb 15 12
456 cccc 15 10 1
update A set A.sign=1 from 表 A where not exists(select 1 from 表 where NO=a.NO and Value1-Value2>A.Value1-A.Value2)
update t
set Sign=1
from 表 t
where not exists(select 1
from 表
where [No]=t.[No]
and (Value1-Value2)
>(t.Value1-t.Value2)
)
update
a
set
Sign = 1
from
表 a
where
not exists(select
1
from
表
where
No=a.No
and
(Value1-Value2)>(a.Value1-a.Value2))
update a set [Sign]=1
from 表 a,(
select [no]name,val=max(Value1-Value2)
from 表
group by [no],name
)b where a.[no]=b.[no]
and a.name=b.name
and (a.Value1-a.Value2)=b.val
--测试数据
declare @t table(No varchar(10),Name varchar(10),Value1 int,Value2 int,Sign char(1))
insert @t select 123,aaaa,12,10,
insert @t select 123,bbbb,12,11,
insert @t select 123,cccc,12,9 ,
insert @t select 456,aaaa,15,10,
insert @t select 456,bbbb,15,12,
insert @t select 456,cccc,15,10,
update @t set Sign=1 where No+ +convert(varchar(10),Value1-Value2) in(select No+ +convert(varchar(10),max(Value1-Value2)) from t group by No)
select * from @t
--结果
No Name Value1 Value2 Sign
---------- ---------- ----------- ----------- ----
123 aaaa 12 10
123 bbbb 12 11
123 cccc 12 9 1
456 aaaa 15 10 1
456 bbbb 15 12
456 cccc 15 10 1
update table set sign=1 from (select [no],max(value1-value2) val from table group by [no] )t where t.[no]=no and t.val=(value1-value2)
