求一条sql语句,按条件查询过期的客户信息
建表：

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、 无父子记录关系的过期全部查出。


------解决方案--------------------
另外两种写法也行

SQL code

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;