根据提供的数据求出该日期所在的季度

 1 select
 2 T1.INDEXCODE
 3 ,T1.CREATETIME 
 4 ,CASE when T1.CREATETIME = 
 5 (case when to_char(SYSDATE,‘MM‘)/3-1 <= 0 
 6 then TRIM(to_char(SYSDATE,‘YYYY‘)-1)||‘-‘||TRIM(4+(to_char(SYSDATE,‘MM‘)/3-1))
 7 else TRIM(to_char(SYSDATE,‘YYYY‘))||‘-‘||TRIM(to_char(SYSDATE,‘MM‘)/3-1)
 8 end) then ‘本季度‘ 
 9 when T1.CREATETIME = 
10 (case when to_char(SYSDATE,‘MM‘)/3-2 <= 0 
11 then TRIM(to_char(SYSDATE,‘YYYY‘)-1)||‘-‘||TRIM(4+(to_char(SYSDATE,‘MM‘)/3-2))
12 else TRIM(to_char(SYSDATE,‘YYYY‘))||‘-‘||TRIM(to_char(SYSDATE,‘MM‘)/3-2)
13 end)
14 then ‘前1季度‘ 
15 when T1.CREATETIME = 
16 (case when to_char(SYSDATE,‘MM‘)/3-3 <= 0 
17 then TRIM(to_char(SYSDATE,‘YYYY‘)-1)||‘-‘||TRIM(4+(to_char(SYSDATE,‘MM‘)/3-3))
18 else TRIM(to_char(SYSDATE,‘YYYY‘))||‘-‘||TRIM(to_char(SYSDATE,‘MM‘)/3-3)
19 end) 
20 then ‘前2季度‘ 
21 when T1.CREATETIME = 
22 (case when to_char(SYSDATE,‘MM‘)/3-4 <= 0 
23 then TRIM(to_char(SYSDATE,‘YYYY‘)-1)||‘-‘||TRIM(4+(to_char(SYSDATE,‘MM‘)/3-4))
24 else TRIM(to_char(SYSDATE,‘YYYY‘))||‘-‘||TRIM(to_char(SYSDATE,‘MM‘)/3-4)
25 end) 
26 then ‘前3季度‘ 
27 when T1.CREATETIME = 
28 (case when to_char(SYSDATE,‘MM‘)/3-5 <= 0 
29 then TRIM(to_char(SYSDATE,‘YYYY‘)-1)||‘-‘||TRIM(4+(to_char(SYSDATE,‘MM‘)/3-5))
30 else TRIM(to_char(SYSDATE,‘YYYY‘))||‘-‘||TRIM(to_char(SYSDATE,‘MM‘)/3-5)
31 end)
32 then ‘前4季度‘ END as 指标
33 from TableA  t1
34 INNER JOIN TableB  t2 on T1.indexcode = t2.indexcode 
35 where T2.indexcode in (‘I1301000019‘)

 1 select  
 2 CREATETIME 
 3 ,CASE when T1.CREATETIME = ‘${jd}‘ then ‘本期‘ 
 4 when T1.CREATETIME = 
 5 (case when SUBSTR(‘${jd}‘,-1,1)-1<=0 
 6 then SUBSTR(‘${jd}‘,1,4)-1||‘-‘||(4+SUBSTR(‘${jd}‘,-1,1)-1)
 7 else SUBSTR(‘${jd}‘,1,4)||‘-‘||(SUBSTR(‘${jd}‘,-1,1)-1)
 8 end)
 9 then ‘前1期‘ 
10 when T1.CREATETIME = 
11 (case when SUBSTR(‘${jd}‘,-1,1)-2<=0 
12 then SUBSTR(‘${jd}‘,1,4)-1||‘-‘||(4+SUBSTR(‘${jd}‘,-1,1)-2)
13 else SUBSTR(‘${jd}‘,1,4)||‘-‘||(SUBSTR(‘${jd}‘,-1,1)-2)
14 end) 
15 then ‘前2期‘ 
16 when T1.CREATETIME = 
17 (case when SUBSTR(‘${jd}‘,-1,1)-3<=0 
18 then SUBSTR(‘${jd}‘,1,4)-1||‘-‘||(4+SUBSTR(‘${jd}‘,-1,1)-3)
19 else SUBSTR(‘${jd}‘,1,4)||‘-‘||(SUBSTR(‘${jd}‘,-1,1)-3)
20 end) 
21 then ‘前3期‘ 
22 when T1.CREATETIME = 
23 (case when SUBSTR(‘${jd}‘,-1,1)-4<=0 
24 then SUBSTR(‘${jd}‘,1,4)-1||‘-‘||(4+SUBSTR(‘${jd}‘,-1,1)-4)
25 else SUBSTR(‘${jd}‘,1,4)||‘-‘||(SUBSTR(‘${jd}‘,-1,1)-4)
26 end)
27 then ‘前4期‘ END as 季度
28 from  table

select replace(str(substring(‘2019-08-01‘,6,2)*1)+‘/‘+str(right(‘2019-08-01‘,2)*1),‘ ‘,‘‘)