sql 技巧查询

xhdwmc, xhdwdm, sum(case when FPLXDM=‘004‘ then 1 ELSE 0 end ) zp, sum(case when FPLXDM=‘007‘ then 1 ELSE 0 end ) pp, sum(case when FPLXDM=‘026‘ then 1 ELSE 0 end ) dp FROM bw_fpzxx WHERE TRUNC(kprq) = TRUNC(SYSDATE) AND LYBZ != ‘08‘ AND XHDWDM != ‘11010120181019084‘ And FPLXDM in(‘004‘,‘007‘,‘026‘) GROUP BY xhdwdm, xhdwmc ORDER BY xhdwdm

   

Oracle中按连续号码分段

  oracle 连续号码分段，我们如何分组：

  

select min(t.fphm),max(t.fphm) ,count(1), (max(t.fphm)-min(t.fphm)+1)

from 

(select fphm, (fphm-rownum) as tnum

from bw_fpzxx s
where s.XHDWDM=‘91310101132208081P‘ and s.fplxdm=‘026‘

and  s.kprq >= TO_DATE (
    ‘2019-07-01‘,
    ‘yyyy-mm-dd hh24:mi:ss‘
)
AND s.kprq <= TO_DATE (
    ‘2019-07-31 23:59:59‘,
    ‘yyyy-mm-dd hh24:mi:ss‘
)


order by fphm  ) t

group by t.tnum

 

    

 

sql 技巧查询

标签：rac   case   ase   编号   style   ref   strong   htm   href