1、iBatis会自动缓存每条查询语句的列名映射,所有如果你动态生成列名的话就有可能造成查询列名无效的问题。
ibaits 会缓存查询的meta信息,在生成动态列时一定要加上 remapResults="true"。
2、oracle查询sql中,想使用数字或特殊字符作列的别名时,可以用“”引起来。
3、oracle行转列,事先需要确定查询的列信息,在列用sum和decode函数汇总
如:
<select id="queryZjhHzByRq" parameterClass="com.athena.paicjh.entity.paiczjhjs.Paiczjhjs" resultClass="java.util.HashMap" remapResults="true">
select zcwlh || '(' || sum(sl) || ')' zcwlh
<iterate property="beanList">
,sum(decode(rksj, #beanList[].rksj#, sl, 0)) "$beanList[].rksj$"
</iterate>
from (select to_char(jz.rksj, 'yyyy-mm-dd') rksj,
jz.zcwlh,
sum(nvl(jz.sl, 0)) sl,
jz.ptbz
from jh_zjh jz
where jz.usercenter = #usercenter#
and jz.rksj >= to_date(#start#, 'yyyy-mm-dd')
and jz.rksj <![CDATA[<=]]> to_date(#end#, 'yyyy-mm-dd')
<isNotEmpty prepend="and" property="zcwlh">
zcwlh like $zcwlh$
</isNotEmpty>
group by jz.rksj, jz.zcwlh, jz.ptbz
union all
select to_char(to_date(rksj, 'yyyymm'), 'yyyy-mm') rksj,
zcwlh,
sum(nvl(sl, 0)) sl,
'2' ptbz
from jh_yzjh
where usercenter = #usercenter#
and drbz = '0'
and to_date(rksj || '01', 'yyyymmdd') >= to_date(#start#, 'yyyy-mm-dd')
and to_date(rksj || '01', 'yyyymmdd') <![CDATA[<=]]> to_date(#end#, 'yyyy-mm-dd')
<isNotEmpty prepend="and" property="zcwlh">
zcwlh like $zcwlh$
</isNotEmpty>
group by rksj, zcwlh) t
group by zcwlh
order by zcwlh
</select>