有个表:
id sort value
1 1
1 2
1 3
2 1
2 2
2 3
2 4
2 5
... ...
想把每个id中sort最小的,也就是1,update value = 1,最大的,update value=2,其余的update value = 4,能用一句sql写出来吗?
id sort value
1 1
1 2
1 3
2 1
2 2
2 3
2 4
2 5
... ...
想把每个id中sort最小的,也就是1,update value = 1,最大的,update value=2,其余的update value = 4,能用一句sql写出来吗?
insert into test(id,sort) values(1,2);
insert into test(id,sort) values(1,3);
insert into test(id,sort) values(2,1);
insert into test(id,sort) values(2,2);
insert into test(id,sort) values(2,3);
insert into test(id,sort) values(2,4);
insert into test(id,sort) values(2,5);
commit;select * from testupdate test a
set value = (select decode(b.sort, c.max, 2, c.min, 1, 4)
from test b,
(select max(sort) max, min(sort) min from test) c
where b.id = a.id
and b.sort = a.sort)