有表
a1 a2 a3
A 11 8
A 12 8
A 13 9
B 21 9
B 22 9
C 31 5
C 32 5
C 33 5对于同一个a1,a2最小的那条纪录的a3才保留原值,否则变为0,操作后结果为:
a1 a2 a3
A 11 8
A 12 0
A 13 9
B 21 0
B 22 0
C 31 5
C 32 0
C 33 0问sql代码如何实现,谢谢!
a1 a2 a3
A 11 8
A 12 8
A 13 9
B 21 9
B 22 9
C 31 5
C 32 5
C 33 5对于同一个a1,a2最小的那条纪录的a3才保留原值,否则变为0,操作后结果为:
a1 a2 a3
A 11 8
A 12 0
A 13 9
B 21 0
B 22 0
C 31 5
C 32 0
C 33 0问sql代码如何实现,谢谢!
set a3=case when a3=(select min(a3) from tb where a1=a.a1) then a3 else 0 end
from tb a
按照你的逻辑,结果不是应该这样吗:
A 11 8
A 12 0
A 13 0
B 21 9
B 22 9
C 31 5
C 32 5
C 33 5
declare @t table(a1 varchar(4),a2 int,a3 int)
insert into @t values('A',11,8)
insert into @t values('A',12,8)
insert into @t values('A',13,9)
insert into @t values('B',21,9)
insert into @t values('B',22,9)
insert into @t values('C',31,5)
insert into @t values('C',32,5)
insert into @t values('C',33,5) update t set a3=0 from @t t where exists(select 1 from @t where a1=t.a1 and a2<t.a2)select * from @t/*
a1 a2 a3
---- ----------- -----------
A 11 8
A 12 0
A 13 0
B 21 9
B 22 0
C 31 5
C 32 0
C 33 0
*/