create table inter_log(id number(10),damage_id varchar2(20),interface_type varchar2(20),log_name varchar2(20))
select * from inter_log;insert into inter_log (id,damage_id,interface_type,log_name) values (1,'001','2','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (2,'001','2','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (3,'001','2','结案');insert into inter_log (id,damage_id,interface_type,log_name) values (4,'001','3','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (5,'001','3','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (6,'001','3','结案');insert into inter_log (id,damage_id,interface_type,log_name) values (7,'002','3','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (8,'002','3','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (9,'002','3','结案');insert into inter_log (id,damage_id,interface_type,log_name) values (10,'003','2','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (11,'003','2','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (12,'003','2','结案');
damage_id:案件号码。
interface_type:接口类型。
log_name:案件操作名称。一个案子对应的接口类型应该有2和3两类(例如案子001)。
但是案子002和003只有一个接口类型。
怎么才能过滤出002这样的案子呢?也就是怎么才能过滤出接口缺少类型3的案件damage_id呢?
select * from inter_log;insert into inter_log (id,damage_id,interface_type,log_name) values (1,'001','2','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (2,'001','2','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (3,'001','2','结案');insert into inter_log (id,damage_id,interface_type,log_name) values (4,'001','3','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (5,'001','3','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (6,'001','3','结案');insert into inter_log (id,damage_id,interface_type,log_name) values (7,'002','3','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (8,'002','3','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (9,'002','3','结案');insert into inter_log (id,damage_id,interface_type,log_name) values (10,'003','2','报案');
insert into inter_log (id,damage_id,interface_type,log_name) values (11,'003','2','立案');
insert into inter_log (id,damage_id,interface_type,log_name) values (12,'003','2','结案');
damage_id:案件号码。
interface_type:接口类型。
log_name:案件操作名称。一个案子对应的接口类型应该有2和3两类(例如案子001)。
但是案子002和003只有一个接口类型。
怎么才能过滤出002这样的案子呢?也就是怎么才能过滤出接口缺少类型3的案件damage_id呢?
where not exisit (select '' from inter_log b
where a.damage_id= b.damage_id
and b.interface_type=3);
select *
from inter_log a
where not exists (select 1 from inter_log b where a.damage_id = b.damage_id and b.interface_type = '3');
select * from inter_log a
where not exists (select '' from inter_log b
where a.damage_id= b.damage_id
and b.interface_type='3');