刚才忽略了一个问题
就是还有一种数据
没有A级全是B级的数据
将测试数据'N1200000000000000000006' 全部改为B级 再试下
这样该怎么写呢insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '8 ', 'N1200000000000000000006', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '9 ', 'N1200000000000000000006', 'B级 ', '0', '1');
就是还有一种数据
没有A级全是B级的数据
将测试数据'N1200000000000000000006' 全部改为B级 再试下
这样该怎么写呢insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '8 ', 'N1200000000000000000006', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '9 ', 'N1200000000000000000006', 'B级 ', '0', '1');
create table M_CBA_REGISTERINFO(
CBA_ID CHAR(23) not null,
seq1 CHAR(3) not null,
AJ_ID CHAR(23) not null,
CBA_LEVEL CHAR(4) not null,
DEL CHAR(1) not null,
SECRECY CHAR(1) not null
);delete from M_CBA_REGISTERINFO a
where exists (select 1 from M_CBA_REGISTERINFO b
where b.AJ_ID=a.AJ_ID
and (b.CBA_LEVEL<a.CBA_LEVEL
or (b.CBA_LEVEL=a.CBA_LEVEL and b.rowid<a.rowid))
);