Update简单问题？

[insert2003]

有下表：

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

[tangqijun199]

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)

[vivianfdlpw]

update t
set Sign='1'
from 表 t
where not exists(select 1
from 表
where [No]=t.[No]
and (Value1-Value2)
>(t.Value1-t.Value2)
)

[wgsasd311]

update tbname set sign=1
from tbname a,(select [no],min(value2) as v2 from tbname group by [no]) b
where a.no=b.no and a.value2=b.v2

[子陌红尘]

update
a
set
Sign = '1'
from
表 a
where
not exists(select
1
from
表
where
No=a.No
and
(Value1-Value2)>(a.Value1-a.Value2))

[zjcxc]

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

[a2048]

up!

[insert2003]

邹建大哥，这个表要按 No 分组来更新，
但又只能通过 No,Name 来确定唯一行，
如果按你写的代码，会不会将所有行都更新为 '1'了？
在我机上测试是全部为'1'了，是不是我没有描述清楚？

前面几位仁兄在我机上测试得到的结果是正确的，但看得不是非常明白

[xueguang]

--测试数据
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

（所影响的行数为 6 行）

[xueguang]

--少了个@
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

[gimy007]

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)

[gimy007]

create table t(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' from (select [no],max(value1-value2) val from t group by [no] )t1 where t1.[no]=t.[no] and t1.val=(value1-value2)

select * from t

[gimy007]

--结果显示
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

--删除数据
drop table t

[tangqijun199]

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))


更新表的字段sign='1'

当不存在有NO相等但是且(Value1-Value2)>(A.Value1-A.Value2)

意思是没有对于不同的NO取最大的(Value1-Value2)的记录出来更新.