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

Question

本帖最后由 k_cool9 于 2012-05-09 15:42:53 编辑

Answer 1

Isex，一看以为是性别....
父过期子没过期呢？

Answer 2

/*
1、 如果父子记录都过期，查询出的结果只能是父记录。
2、 如果子记录过期，父记录没有过期，查询出的结果没有记录。
3、 无父子记录关系的过期全部查出。
*/select aid,aname,aphone,custno,subid,subcustno,isex from (
SELECT A.AID,A.ANAME,A.APHONE,A.CUSTNO,A.ISEX,
B.AID as subid,B.ANAME as subname,B.APHONE as subphone,B.CUSTNO as subcustno,B.ISEX as expired
FROM T_TEST A LEFT JOIN T_TEST B
ON A.ZHCUSTNO = B.CUSTNO
WHERE (a.isex = 2 and b.isex=2 )
--这两种情况是否也要查出
--or (a.isex=2 and b.isex=1)--父集过期，子集没过期 
--or (a.isex=1 and b.isex=1)--父子集均未过期
or (b.aid is null)
)AID                    ANAME                                                                                                APHONE                                                                                               CUSTNO                                                                                               SUBID                  SUBCUSTNO                                                                                            ISEX    
---------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------- ---------------------------------------------------------------------------------------------------- --------
1003                   王五                                                                                                 15076992233                                                                                          GMSZ0099                                                                                             1004                   SZ000099                                                                                             2       
1008                   王宝强                                                                                               15076915233                                                                                          GMSZ5566                                                                                             1009                   SZ005566                                                                                             2       
1014                   安华                                                                                                 15276992233                                                                                          GMSZ1199                                                                                             1015                   SZ001199                                                                                             2      
1017                   大浪                                                                                                 13776995213                                                                                          SZ002255                                                                                                                                                                                                                         2      
1015                   天涯                                                                                                 13276995213                                                                                          SZ001199                                                                                                                                                                                                                         2      
1013                   刘强                                                                                                 15270795533                                                                                          SZ001234                                                                                                                                                                                                                         1      
1012                   李伍华                                                                                               15071795833                                                                                          GMSZ3399                                                                                                                                                                                                                         2      
1011                   李小华                                                                                               15276795533                                                                                          SZ004466                                                                                                                                                                                                                         1      
1010                   李冬华                                                                                               15076795833                                                                                          GMSZ2299                                                                                                                                                                                                                         2      
1009                   王小强                                                                                               15056915233                                                                                          SZ005566                                                                                                                                                                                                                         2      
1007                   李冬冬                                                                                               15076795233                                                                                          SZ008866                                                                                                                                                                                                                         2      
1005                   张三华                                                                                               15076995233                                                                                          GMSZ5599                                                                                                                                                                                                                         2      
1004                   刘芳                                                                                                 13076995213                                                                                          SZ000099                                                                                                                                                                                                                         2     
1002                   李四                                                                                                 15073995233                                                                                          SZ008899                                                                                                                                                                                                                         2                                                                                                    

Answer 3

父集过期，子集没过期  
父子集均未过期
这两种情况都不需要查询出来，
我写出来的是这一下两条。
1.select * from a_test fu where fu.custno not in 
(select zi.zhcustno from a_test zi where zi.zhcustno is not null) and fu.isex =2 2.     select fu.*
from a_test fu 
where 
(fu.zhcustno in (select zi.custno from a_test zi where zi.zhcustno is null) and fu.isex =2 ) 
or 
(fu.zhcustno is null and fu.custno not in 
(select zi.zhcustno from a_test zi where zi.zhcustno is not null) and fu.isex =2 ) 
 
不过放到正式数据中的时候效率太低，一万多条需要三十六秒才能查询出来。 并且第一条的数据比第二条的数据少了两条。其实我觉得我上面说的三种逻辑条件其实就是两种情况，一种就是不需要管子记录，只要父记录过期的就查询出来，不管子记录过不过期。另一种就是 无父子记录关系的过期全部查出。这是测试结果
1003 王五 15076992233 GMSZ0099 SZ000099 2
1005 张三华 15076995233 GMSZ5599 2
1008 王宝强 15076915233 GMSZ5566 SZ005566 2
1010 李冬华 15076795833 GMSZ2299 2
1012 李伍华 15071795833 GMSZ3399 2
1014 安华 15276992233 GMSZ1199 SZ001199 2

Answer 4

就是说，需要查询的只是父子都过期，或者过期的孤家寡人呗，而且只需要查出最高的一级
with tmp1 as
 (select t1.*,
         t2.aid      aid2,
         t2.aname    aname2,
         t2.aphone   aphone2,
         t2.custno   custno2,
         t2.zhcustno zhcustno2,
         t2.isex     isex2
    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
 where isex = '2' and zhcustno is null
minus
select aid2, aname2, aphone2, custno2, zhcustno2, isex2
  from tmp1)
order by 1;

Answer 5

另外两种写法也行
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分别建两个索引

Answer 6

wildwave 的两条执行结果 效率都是34秒 比我之前的39秒还是快了5秒，想看看还有效率更快点的没

Answer 7

如果表中的数据都像你给出的这么规矩的话(父级GMSZ开头，子SZ开头)，那就方便了select t1.*
from a_test t1,a_test t2
where t1.custno like 'GMSZ%'
  and t1.isex='2'
  and t2.custno(+) like 'SZ%'
  and t1.zhcustno=t2.custno(+)
  and t2.isex(+)='2'
  and (t1.zhcustno is null or t2.aid is not null)
order by 1;这个效率会比较高一些

Answer 8

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;
 这个我再一试然来只要九秒了，很快。 我想问下with tmp1 as这是什么意思，还有order by 1 有什么意义？？