통계 최종쿼리

select * from 
   		(select 
	   		/* business.usr.sub.lifewtrcy.dao.LifewtRcyDAO.selectRcyRecord */
			tfi.fclt_sn
			, tra.fclt_sn 
		     , tra.fclt_nm                                     
		     , TO_CHAR(tra.reg_date, 'yyyy-mm-dd')	AS "reg_date"
		     , tra.fclt_year || '.' || tra.fclt_mth		AS "yearMth"
		     , tcc.comm_nm							AS "sgg_nm"
		     , a.comm_nm 							AS "pr_mthd_dp1"
			 , b.comm_nm 							as "pr_mthd_dp2"
			 , tra.sgg_cd							As "sggCd"
		     , max(case when tra.sts_wst_type_cd = 'RCY0501' then tra.tot_amt end ) as "RCY0501"
		     , max(case when tra.sts_wst_type_cd = 'RCY0502' then tra.tot_amt end ) as "RCY0502"
		     , max(case when tra.sts_wst_type_cd = 'RCY0503' then tra.tot_amt end ) as "RCY0503"
		     , max(case when tra.sts_wst_type_cd = 'RCY0504' then tra.tot_amt end ) as "RCY0504"
		     , max(case when tra.sts_wst_type_cd = 'RCY0505' then tra.tot_amt end ) as "RCY0505"
		     , max(case when tra.sts_wst_type_cd = 'RCY0506' then tra.tot_amt end ) as "RCY0506"
		     , max(case when tra.sts_wst_type_cd = 'RCY0507' then tra.tot_amt end ) as "RCY0507"
		     , max(case when tra.sts_wst_type_cd = 'RCY0508' then tra.tot_amt end ) as "RCY0508"
		     , max(case when tra.sts_wst_type_cd = 'RCY0509' then tra.tot_amt end ) as "RCY0509"
		     , max(case when tra.sts_wst_type_cd = 'RCY0510' then tra.tot_amt end ) as "RCY0510"
		     , max(case when tra.sts_wst_type_cd = 'RCY0511' then tra.tot_amt end ) as "RCY0511"
		     , max(case when tra.sts_wst_type_cd = 'RCY0512' then tra.tot_amt end ) as "RCY0512"
		     , max(case when tra.sts_wst_type_cd = 'RCY0513' then tra.tot_amt end ) as "RCY0513"
		     , max(case when tra.sts_wst_type_cd = 'RCY0514' then tra.tot_amt end ) as "RCY0514"
		     , max(case when tra.sts_wst_type_cd = 'RCY0515' then tra.tot_amt end ) as "RCY0515"
		     , max(case when tra.sts_wst_type_cd = 'RCY0516' then tra.tot_amt end ) as "RCY0516"
		     , max(case when tra.sts_wst_type_cd = 'RCY0517' then tra.tot_amt end ) as "RCY0517"
		     , max(case when tra.sts_wst_type_cd = 'RCY0518' then tra.tot_amt end ) as "RCY0518"
		     , max(case when tra.sts_wst_type_cd = 'RCY0519' then tra.tot_amt end ) as "RCY0519"
		     , max(case when tra.sts_wst_type_cd = 'RCY0520' then tra.tot_amt end ) as "RCY0520"
		     , max(case when tra.sts_wst_type_cd = 'RCY0521' then tra.tot_amt end ) as "RCY0521"
		from t_record_amt_life_rcy tra
		left join t_fclt_info tfi
				on tra.fclt_sn = tfi.fclt_sn 
		LEFT JOIN t_comm_cd tcc 
				ON tra.sgg_cd = tcc.comm_code
		LEFT JOIN t_comm_cd a
				ON  tfi.pr_mthd_dp1  = a.comm_code
		left join t_comm_cd b 
				on tfi.pr_mthd_dp2 = b.comm_code 
		<trim prefix="WHERE" prefixOverrides="and">
			<if test='fcltNm != null'>
			 	and tra.fclt_nm like '%'||#{fcltNm}||'%'
			</if>
			<if test="sggCd != null and sggCd != ''">
				AND tra.sgg_cd = #{sggCd}
			</if>
			<if test="prMthdDp1 != null and prMthdDp1 != ''">
				AND tfi.pr_mthd_dp1 = #{prMthdDp1}
			</if>
			<if test="prMthdDp2 != null and prMthdDp2 != ''">
				AND tfi.pr_mthd_dp2 = #{prMthdDp2}
			</if>
			<if test="beginYear != null and beginYear != ''">
				AND tra.fclt_year || '.' || tra.fclt_mth between #{beginYear}|| '.' || #{beginMonth} and #{endYear}|| '.' || #{endMonth} 
			</if>
		</trim>	
		group by tfi.fclt_sn, tra.fclt_sn ,tra.sido  ,tra.sgg  ,tra.fclt_nm   ,tra.reg_date, tra.fclt_year || '.' || tra.fclt_mth	,tcc.comm_nm, tra.sgg_cd	 , a.comm_nm  , b.comm_nm 
		order by tfi.fclt_sn, tra.fclt_sn ,tra.sido ,tra.sgg  ,tra.fclt_nm   ,tra.reg_date, tra.fclt_year || '.' || tra.fclt_mth	,tcc.comm_nm, tra.sgg_cd	  , a.comm_nm ,b.comm_nm
		)s
		<if test="stsWstTypeCd != null and stsWstTypeCd != ''">
		 	WHERE
			 	<![CDATA[ 
				 	 "${stsWstTypeCd}" > 0 	
			 	]]>
			</if>
		order by s."yearMth" desc

나에겐 최선이었던 쿼리,,,