建表：
create table a_test(
aid number(11) not null primary key,
aname varchar2(100),
aphone varchar2(100),
custno varchar2(100),
zhcustno varchar2(100),
isex varchar2(100)
)
插数据：
insert into a_test values(1001,'张三','15076995233','GMSZ8899','SZ008899','1');
insert into a_test values(1002,'李四','15073995233','SZ008899','','2');
insert into a_test values(1003,'王五','15076992233','GMSZ0099','SZ000099','2');
insert into a_test values(1004,'刘芳','13076995213','SZ000099','','2');
insert into a_test values(1005,'张三华','15076995233','GMSZ5599','','2');
insert into a_test values(1006,'张三丰','13076965233','GMSZ8866','SZ008866','1');
insert into a_test values(1007,'李冬冬','15076795233','SZ008866','','2');
insert into a_test values(1008,'王宝强','15076915233','GMSZ5566','SZ005566','2');
insert into a_test values(1009,'王小强','15056915233','SZ005566','','2');
insert into a_test values(1010,'李冬华','15076795833','GMSZ2299','','2');
insert into a_test values(1011,'李小华','15276795533','SZ004466','','1');
insert into a_test values(1012,'李伍华','15071795833','GMSZ3399','','2');
insert into a_test values(1013,'刘强','15270795533','SZ001234','','1');
insert into a_test values(1014,'安华','15276992233','GMSZ1199','SZ001199','2');
insert into a_test values(1015,'天涯','13276995213','SZ001199','','2');
insert into a_test values(1016,'田英','15176992233','GMSZ2255','SZ002255','1');
insert into a_test values(1017,'大浪','13776995213','SZ002255','','2');
业务逻辑：
Isex 为过期字段 1为未过期 2为已过期
Custno为客户号
Zhcustno 为子客户号
要求：
查询出已过期的客户数据：
1、 如果父子记录都过期，查询出的结果只能是父记录。
2、 如果子记录过期，父记录没有过期，查询出的结果没有记录。
3、 无父子记录关系的过期全部查出。
------解决方案--------------------
另外两种写法也行
with tmp1 as
(select t1.*, t2.aid aid2
from a_test t1, a_test t2
where t1.zhcustno = t2.custno
and t2.isex = '2')
select aid, aname, aphone, custno, zhcustno, isex
from tmp1
where isex = '2'
union all(
select *
from a_test t3
where isex = '2' and zhcustno is null and not exists(
select 1
from tmp1 t4
where t3.aid = t4.aid2))
order by 1;
select t1.*
from a_test t1,a_test t2
where t1.zhcustno=t2.custno
and t1.isex='2' and t2.isex='2'
union all
select *
from a_test t3
where t3.zhcustno is null
and t3.isex='2'
and not exists(select 1 from a_test t4 where t4.zhcustno=t3.custno)
order by 1;
比较一下执行计划，选择效率较高的那个
对zhcustno和custno分别建两个索引