<select id="findTpostSkillresultListBySpostid" resultClass="tpostSkillresultBean" parameterClass="map" remapResults="true">
select s.capgrade,
(select codename from tb_tpost_code where id = s.mpostid) mpostname,
(select codename from tb_tpost_code where id = s.spostid) spostname,
r.id,r.username, r.idcard, to_char(r.scoredate, 'yyyy-mm-dd') scoredate, r.score,
(select ut.sortname from pxgl_unit ut where ut.unitid = r.unitid) unitname_,
r.remark, r.flowsta, r.chksign, r.noexamsign,
mainusername estauser,
to_char(r.estatime,'yyyy-mm-dd') estatime,
i.itemname,
(select capgrade from tb_tpost_userpost
where mpostid = s.mpostid and spostid = s.spostid and idcard = r.idcard) usercapgrade
from tb_tpost_skillresult r,
tb_tpost_skillitem i,
tb_tpost_skillstandar s
where (
(r.noexamsign = 1 and to_char(r.estatime, 'yyyy') = #year#) or
to_char(scoredate, 'yyyy') = #year#
)
and i.standarid = s.id
and i.id = r.yitemid
and i.flowsta = 99
and i.chksign = 1
<isNotEmpty prepend="and" property="itemid">
i.id = #itemid#
</isNotEmpty>
<isNotEmpty prepend="and" property="flowsta">
(r.flowsta >= #flowsta#
<isNotEmpty prepend="or" property="flowsign">
r.flowsta = #SKILLFLOW_DXGSRZ_SH#
</isNotEmpty>
)
</isNotEmpty>
<isNotEmpty prepend="and" property="waitsign">
r.flowsta = #flowsta#
</isNotEmpty>
<isNotEmpty property="unitid">
and r.unitid in ($unitid$)
</isNotEmpty>
<isNotEmpty prepend="and" property="mpostid">
s.mpostid = #mpostid#
</isNotEmpty>
<isNotEmpty prepend="and" property="spostid">
s.spostid = #spostid#
</isNotEmpty>
<isNotEmpty prepend="and" property="capgrade">
s.capgrade = #capgrade#
</isNotEmpty>
<isNotEmpty prepend="and" property="keyword">
(
r.username like '%'||#keyword#||'%'
or r.idcard like '%'||#keyword#||'%'
or to_char(r.scoredate, 'yyyy-mm-dd') like '%'||#keyword#||'%'
)
</isNotEmpty>
order by s.mpostid, s.spostid, s.capgrade,i.id,r.flowsta
</select>
原先代码如上，原谅我是个菜鸟，问下如何将以下代码加入到上面代码中。。。
<isNotEmpty property="capgradeflag">
<isEqual prepend="and" property="capgradeflag" compareValue="1"> capgrade = (select capgrade from tb_tpost_userpost where mpostid = s.mpostid and spostid = s.spostid and idcard = r.idcard)</isEqual>
<isEqual prepend="and" property="capgradeflag" compareValue="2"> nvl(capgrade, 0) != nvl((select capgrade from tb_tpost_userpost where mpostid = s.mpostid and spostid = s.spostid and idcard = r.idcard), 0)</isEqual>
</isNotEmpty>
拜托了。。。
加入的代码如下，你试试。
<select id="findTpostSkillresultListBySpostid" resultClass="tpostSkillresultBean" parameterClass="map" remapResults="true">
select s.capgrade,
(select codename from tb_tpost_code where id = s.mpostid) mpostname,
(select codename from tb_tpost_code where id = s.spostid) spostname,
r.id,r.username, r.idcard, to_char(r.scoredate, 'yyyy-mm-dd') scoredate, r.score,
(select ut.sortname from pxgl_unit ut where ut.unitid = r.unitid) unitname_,
r.remark, r.flowsta, r.chksign, r.noexamsign,
mainusername estauser,
to_char(r.estatime,'yyyy-mm-dd') estatime,
i.itemname,
(select capgrade from tb_tpost_userpost
where mpostid = s.mpostid and spostid = s.spostid and idcard = r.idcard) usercapgrade
from tb_tpost_skillresult r,
tb_tpost_skillitem i,
tb_tpost_skillstandar s
where (
(r.noexamsign = 1 and to_char(r.estatime, 'yyyy') = #year#) or
to_char(scoredate, 'yyyy') = #year#
)
and i.standarid = s.id
and i.id = r.yitemid
and i.flowsta = 99
and i.chksign = 1
<isNotEmpty prepend="and" property="itemid">
i.id = #itemid#
</isNotEmpty>
<isNotEmpty prepend="and" property="flowsta">
(r.flowsta >= #flowsta#
<isNotEmpty prepend="or" property="flowsign">
r.flowsta = #SKILLFLOW_DXGSRZ_SH#
</isNotEmpty>
)
</isNotEmpty>
<isNotEmpty prepend="and" property="waitsign">
r.flowsta = #flowsta#
</isNotEmpty>
<isNotEmpty property="unitid">
and r.unitid in ($unitid$)
</isNotEmpty>
<isNotEmpty prepend="and" property="mpostid">
s.mpostid = #mpostid#
</isNotEmpty>
<isNotEmpty prepend="and" property="spostid">
s.spostid = #spostid#
</isNotEmpty>
<isNotEmpty prepend="and" property="capgrade">
s.capgrade = #capgrade#
</isNotEmpty>
<isNotEmpty prepend="and" property="keyword">
(
r.username like '%'||#keyword#||'%'
or r.idcard like '%'||#keyword#||'%'
or to_char(r.scoredate, 'yyyy-mm-dd') like '%'||#keyword#||'%'
)
</isNotEmpty>
<!-- 加入的代码 begin -->
<isNotEmpty property="capgradeflag">
<isEqual prepend="and" property="capgradeflag" compareValue="1"> capgrade = (select capgrade from tb_tpost_userpost where mpostid = s.mpostid and spostid = s.spostid and idcard = r.idcard)</isEqual>
<isEqual prepend="and" property="capgradeflag" compareValue="2"> nvl(capgrade, 0) != nvl((select capgrade from tb_tpost_userpost where mpostid = s.mpostid and spostid = s.spostid and idcard = r.idcard), 0)</isEqual>
</isNotEmpty>
<!-- 加入的代码 end -->
order by s.mpostid, s.spostid, s.capgrade,i.id,r.flowsta
</select>