数据库两列数据，第三列数据最大最小相差50的

需求  根据同第一列 第二列相同  第三列数据>50 的统计结果

 

 

with p1 as (
select z.fldcztime,to_char(z.fldcztime,'yyyymmdd') as rq, t.accountname, z.snumber, z.lxcode, z.fldckuser,
 SUBSTR(t.ywbs,2) YWBS
  from wz_zdwzzbinfo z, t_zdddress t
 where z.lxcode = 'yssb'
   and z.fldstate = '待安装'
   and z.snumber = t.snumber
   AND t.ywbs LIKE 'B%' and t.ywbs not LIKE 'B0%' and t.ywbs not LIKE 'BO%' and t.ywbs not LIKE 'BT%' 
   and instr(t.ywbs,chr(10))=0 and instr(t.ywbs,chr(13))=0 and instr(t.ywbs,' ')=0 and instr(t.ywbs,' ')=0
   and instr(t.ywbs,'/')=0  and instr(t.ywbs,'~')=0 and t.ywbs is not null
   and instr(t.ywbs,'割')=0 and instr(t.ywbs,'故')=0 and instr(t.ywbs,'、')=0 and z.fldcztime is not null
 group by t.ywbs,
          z.fldcztime,
          z.snumber,
          z.lxcode,
          z.fldckuser,
          t.accountname
          ),
 P2 AS (         
 select p1.RQ,P1.ACCOUNTNAME ,count(p1.snumber) as hs from p1 group by p1.RQ,P1.ACCOUNTNAME  ),
 p3 as (
 select p2.rq,p2.accountname,p2.hs,
 (select max(p1.YWBS) from p1 where p1.rq=p2.rq and p1.accountname=p2.accountname) as maxbh,
 (select min(p1.YWBS) from p1 where p1.rq=p2.rq and p1.accountname=p2.accountname) as minbh
  from p2
 ) ,
 p4 as (
 select p3.RQ,P3.ACCOUNTNAME from p3 where (to_number(p3.maxbh)-to_number(p3.minbh))>50)
 select * from p1 where exists (select 1 from p4 where p4.rq=p1.rq and p4.ACCOUNTNAME=p1.ACCOUNTNAME)