表:
create table M_CBA_REGISTERINFO(
CBA_ID CHAR(23) not null,
SEQUENCE 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
)
测试数据:
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '5 ', 'N1200000000000000000001', 'A级 ', '0', '1' );
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '7 ', 'N1200000000000000000007', 'A级 ', '0', '1' );
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '9 ', 'N1200000000000000000006', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '10 ', 'N1200000000000000000001', 'A级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '6 ', 'N1200000000000000000005', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '1 ', 'N1200000000000000000005', 'A级 ', '0', '1');
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', '2 ', 'N1200000000000000000007', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '3 ', 'N1200000000000000000007', 'A级 ', '0', '1');要求:1、删除重复数据2、同一 AJ_ID 下同时存在A、B级别时删除B 保留A
语句执行结果应该为:
1 CB410800000000200912016 5 N1200000000000000000001 A级 0 1
2 CB410800000000200912016 7 N1200000000000000000007 A级 0 1
3 CB410800000000200912016 9 N1200000000000000000006 B级 0 1
4 CB410800000000200912016 1 N1200000000000000000005 A级 0 1
create table M_CBA_REGISTERINFO(
CBA_ID CHAR(23) not null,
SEQUENCE 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
)
测试数据:
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '5 ', 'N1200000000000000000001', 'A级 ', '0', '1' );
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '7 ', 'N1200000000000000000007', 'A级 ', '0', '1' );
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '9 ', 'N1200000000000000000006', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '10 ', 'N1200000000000000000001', 'A级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '6 ', 'N1200000000000000000005', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '1 ', 'N1200000000000000000005', 'A级 ', '0', '1');
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', '2 ', 'N1200000000000000000007', 'B级 ', '0', '1');
insert into M_CBA_REGISTERINFO (CBA_ID, SEQUENCE, AJ_ID, CBA_LEVEL, DEL, SECRECY)
values ('CB410800000000200912016', '3 ', 'N1200000000000000000007', 'A级 ', '0', '1');要求:1、删除重复数据2、同一 AJ_ID 下同时存在A、B级别时删除B 保留A
语句执行结果应该为:
1 CB410800000000200912016 5 N1200000000000000000001 A级 0 1
2 CB410800000000200912016 7 N1200000000000000000007 A级 0 1
3 CB410800000000200912016 9 N1200000000000000000006 B级 0 1
4 CB410800000000200912016 1 N1200000000000000000005 A级 0 1
where exists (select 1 from M_CBA_REGISTERINFO
where AJ_ID=a.AJ_ID
and (CBA_LEVEL<a.CBA_LEVEL
or (CBA_LEVE=a.CBA_LEVEL and SEQUENCE<a.SEQUENCE))
)
where exists ( select 1 from M_CBA_REGISTERINFO t1
where t1.AJ_ID = t.AJ_ID
and ( (t1.CBA_LEVEL = t.CBA_LEVEL and t1.rowid < t.rowid)
ort1.CBA_LEVEL < t.CBA_LEVEL);#1的思路是对的啦,你这里面的重复数据定义是AJ_ID和CBA_LEVEL相同就为重复是吧
SQL> drop table M_CBA_REGISTERINFO;表已丢弃。SQL> create table M_CBA_REGISTERINFO(
2 CBA_ID CHAR(23) not null,
3 seq1 CHAR(3) not null,
4 AJ_ID CHAR(23) not null,
5 CBA_LEVEL CHAR(4) not null,
6 DEL CHAR(1) not null,
7 SECRECY CHAR(1) not null
8 );表已创建。SQL>
SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '5 ', 'N1200000000000000000001', 'A级', '0', '1' );已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '7 ', 'N1200000000000000000007', 'A级', '0', '1' );已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '9 ', 'N1200000000000000000006', 'B级', '0', '1');已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '10 ', 'N1200000000000000000001', 'A级', '0', '1');已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '6 ', 'N1200000000000000000005', 'B级', '0', '1');已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '1 ', 'N1200000000000000000005', 'A级', '0', '1');已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '8 ', 'N1200000000000000000006', 'B级', '0', '1');已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '2 ', 'N1200000000000000000007', 'B级', '0', '1');已创建 1 行。SQL> insert into M_CBA_REGISTERINFO (CBA_ID, seq1, AJ_ID, CBA_LEVEL, DEL, SECRECY)
2 values ('CB410800000000200912016', '3 ', 'N1200000000000000000007', 'A级', '0', '1');已创建 1 行。SQL>
SQL> commit;提交完成。SQL>
SQL> select * from M_CBA_REGISTERINFO;CBA_ID SEQ1 AJ_ID CBA_LEVE DE SE
---------------------------------------------- ------ ---------------------------------------------- -------- -- --
CB410800000000200912016 5 N1200000000000000000001 A级 0 1
CB410800000000200912016 7 N1200000000000000000007 A级 0 1
CB410800000000200912016 9 N1200000000000000000006 B级 0 1
CB410800000000200912016 10 N1200000000000000000001 A级 0 1
CB410800000000200912016 6 N1200000000000000000005 B级 0 1
CB410800000000200912016 1 N1200000000000000000005 A级 0 1
CB410800000000200912016 8 N1200000000000000000006 B级 0 1
CB410800000000200912016 2 N1200000000000000000007 B级 0 1
CB410800000000200912016 3 N1200000000000000000007 A级 0 1已选择9行。SQL>
SQL> delete from M_CBA_REGISTERINFO a
2 where exists (select 1 from M_CBA_REGISTERINFO b
3 where b.AJ_ID=a.AJ_ID
4 and (b.CBA_LEVEL<a.CBA_LEVEL
5 or (b.CBA_LEVEL=a.CBA_LEVEL and b.rowid<a.rowid))
6 );已删除5行。SQL>
SQL> select * from M_CBA_REGISTERINFO;CBA_ID SEQ1 AJ_ID CBA_LEVE DE SE
---------------------------------------------- ------ ---------------------------------------------- -------- -- --
CB410800000000200912016 5 N1200000000000000000001 A级 0 1
CB410800000000200912016 7 N1200000000000000000007 A级 0 1
CB410800000000200912016 9 N1200000000000000000006 B级 0 1
CB410800000000200912016 1 N1200000000000000000005 A级 0 1SQL>
对的
但是他的写法还是有重复数据
事实上A B 级别的同一AJ_ID 唯一的差别就在CBA_LEVEL上
其他列都一样
谢谢你了
crazylaa
也谢谢各位热心的朋友
这个sql搞了一下午了
#1不一样,#4#5一样。
如果插入两条一摸一样得有B没A得,删不掉。你前面说的对sequence不能拿来做<比较得。