create table t(sfzh varchar2(50),df number,jg int); delete from t insert into t values ('441401000000000001',79,0); insert into t values ('441401000000000002',89,0); insert into t values ('441401000000000003',99,0); insert into t values ('441402000000000001',76,0); insert into t values ('441402000000000002',96,0); insert into t values ('441402000000000003',98,0); insert into t values ('441403000000000001',79,0); insert into t values ('441403000000000002',73,0); insert into t values ('441403000000000003',72,0); insert into t values ('441404000000000001',92,0); insert into t values ('441404000000000002',98,0); insert into t values ('441404000000000003',95,0);---随机更新 update t set jg = 1 where exists (select sfzh from (select sfzh, row_number() over(partition by substr(sfzh, 0, 6) order by dbms_random.value) rn from t where df > 90) t1 where rn = 1 and t1.sfzh=t.sfzh)1 441404000000000002 98 0 2 441404000000000003 95 0 3 441401000000000001 79 0 4 441401000000000002 89 0 5 441401000000000003 99 1 6 441402000000000001 76 0 7 441402000000000002 96 0 8 441402000000000003 98 1 9 441403000000000001 79 0 10 441403000000000002 73 0 11 441403000000000003 72 0 12 441404000000000001 92 1
create table t(sfzh varchar2(50),df number,jg int);
delete from t
insert into t values ('441401000000000001',79,0);
insert into t values ('441401000000000002',89,0);
insert into t values ('441401000000000003',99,0);
insert into t values ('441402000000000001',76,0);
insert into t values ('441402000000000002',96,0);
insert into t values ('441402000000000003',98,0);
insert into t values ('441403000000000001',79,0);
insert into t values ('441403000000000002',73,0);
insert into t values ('441403000000000003',72,0);
insert into t values ('441404000000000001',92,0);
insert into t values ('441404000000000002',98,0);
insert into t values ('441404000000000003',95,0);---随机更新
update t
set jg = 1
where exists (select sfzh
from (select sfzh,
row_number() over(partition by substr(sfzh, 0, 6) order by dbms_random.value) rn
from t
where df > 90) t1
where rn = 1 and t1.sfzh=t.sfzh)1 441404000000000002 98 0
2 441404000000000003 95 0
3 441401000000000001 79 0
4 441401000000000002 89 0
5 441401000000000003 99 1
6 441402000000000001 76 0
7 441402000000000002 96 0
8 441402000000000003 98 1
9 441403000000000001 79 0
10 441403000000000002 73 0
11 441403000000000003 72 0
12 441404000000000001 92 1