oracle根据代码分组查询 1天 7天 30天 90天的数量-蒲公英云

oracle根据代码分组查询 1天 7天 30天 90天的数量

桃扇骨 2022-12-31 08:24 402阅读 0赞

-- t-1数据
 select t1.FUNDCODE,
 sum (CASE WHEN TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-1) THEN 1   ELSE 0 END) as"1天",
 sum (CASE WHEN TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-8) THEN 1   ELSE 0 END) as"7天",
 sum (CASE WHEN TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-31) THEN 1   ELSE 0 END) as"30天",
 sum (CASE WHEN TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-90) THEN 1   ELSE 0 END) as"90天"
-- sum(case when  TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-1) then COUNT(1) else 0 end) as 1天
-- sum(case when TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-7)  then  COUNT(1) else 0 end) as 7天,
-- sum(case when TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-30)  then  COUNT(1) else 0 end) as 30天,
-- sum(case when TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-90)  then  COUNT(1) else 0 end) as 90天
from 表1 t1 JOIN  表2 t2 on  t1.FUNDCODE = t2.VC_FUNDCODE
where       t2.VC_REQUESTDATE is not null and TO_DATE(t2.VC_REQUESTDATE,'yyyy-mm-dd') >  TRUNC(SYSDATE-90) and t2.C_CUSTTYPE = 1  and C_BUSINFLAG  in('020','022','039') GROUP BY  t1.FUNDCODE;