求一条sql语句,按条件查询过期的客户信息,该怎么解决
求一条sql语句,按条件查询过期的客户信息,该怎么解决
日期:2014-05-16 浏览次数:20399 次
求一条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 codewith 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;
免责声明: 本文仅代表作者个人观点,与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。