<!-- 查询所有学生医保信息 -->
<select id="queryStudent_insure" parameterClass="com.gosoft.sms.insure.po.Insure_manPo" resultClass="com.gosoft.sms.insure.po.Insure_manPo" >
select * from(
select ROWNUM AS rowIndex, ID, STUDENT_NAME, SEX, CLASS_NAME, STATE, STATE_CODE, SPECIALTY_NAME, ACADEMY_NAME, ACADEMY_CODE, CLASS_CODE, GRADE, NUMBER_NO,
STUDENT_ID, INSURE_STATE, BEGIN_DATE, END_DATE, INSURE_CODE, FEE, IS_BOOR, IS_SOCIALIZED, IS_OUTLANDER, IS_NOFEE, END_AUDITING_STATE, DEF_FEE
from (
select s.ID, s.STUDENT_NAME, s.SEX, s.CLASS_NAME, s.STATE, s.STATE_CODE, s.SPECIALTY_NAME, s.ACADEMY_NAME, s.ACADEMY_CODE, s.CLASS_CODE, s.GRADE, s.NUMBER_NO,
i.STUDENT_ID, i.INSURE_STATE, i.BEGIN_DATE, i.END_DATE, i.INSURE_CODE, i.FEE, i.IS_BOOR, i.IS_SOCIALIZED, i.IS_OUTLANDER, i.IS_NOFEE, i.END_AUDITING_STATE, i.RECEIVE_STATE,
(select FEE from T_INSURE_FEE where ROWNUM = 1) as DEF_FEE
from S_V_STUDENTS s
left join T_INSURE_MAN i on s.ID = i.STUDENT_ID
left join T_CLASSES c on c.CLASS_CODE = s.CLASS_CODE
where 1 = 1
<dynamic>
<isNotEmpty prepend="and" property="id">
<![CDATA[ s.ID = #id:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="student_name" >
<![CDATA[ s.STUDENT_NAME like '%'||#student_name:VARCHAR#||'%' ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="class_code" >
<![CDATA[ s.CLASS_CODE = #class_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="academy_name" >
<![CDATA[ s.ACADEMY_NAME = #academy_name:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="academy_code" >
<![CDATA[ s.ACADEMY_CODE = #academy_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="class_name" >
<![CDATA[ s.CLASS_NAME like '%'||#class_name:VARCHAR#||'%' ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="state_code" >
<![CDATA[ s.STATE_CODE = #state_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="grade" >
<![CDATA[ s.GRADE = #grade:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="insure_state" >
<![CDATA[ nvl(i.INSURE_STATE, '-1') = #insure_state:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="insure_code" >
<![CDATA[ (case nvl(i.INSURE_CODE, '0') when '0' then 0 else 1 end ) = #insure_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="end_auditing_state" >
<![CDATA[ i.END_AUDITING_STATE in($end_auditing_state$) ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="receive_state" >
<![CDATA[ nvl(i.RECEIVE_STATE, '0') = #receive_state:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="ids" >
<![CDATA[ s.ID in($ids$) ]]>
</isNotEmpty>
<isNotEmpty property="teacher_id" prepend="and">
<![CDATA[ c.TEACHER_ID = #teacher_id:VARCHAR# ]]>
</isNotEmpty>
</dynamic>
<dynamic prepend="and" open="(" close=")">
<isNotEmpty prepend="or" property="is_boor" >
<![CDATA[ i.IS_BOOR = #is_boor:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="or" property="is_socialized" >
<![CDATA[ i.IS_SOCIALIZED = #is_socialized:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="or" property="is_outlander" >
<![CDATA[ i.IS_OUTLANDER = #is_outlander:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="or" property="is_nofee" >
<![CDATA[ i.IS_NOFEE = #is_nofee:VARCHAR# ]]>
</isNotEmpty>
</dynamic>
<![CDATA[ order by s.STATE_CODE asc, s.CLASS_CODE desc, s.NUMBER_NO desc) ]]>
<dynamic prepend="WHERE">
<isNotEmpty property="endRowNum">
<![CDATA[ ROWNUM <= #endRowNum#) ]]>
</isNotEmpty>
</dynamic>
<dynamic prepend="WHERE">
<isNotEmpty property="startRowNum">
<![CDATA[ rowIndex >= #startRowNum# ]]>
</isNotEmpty>
</dynamic>
</select>
<!-- 获取所有学生医保信息数量 -->
<select id="queryStudent_insureCount" parameterClass="com.gosoft.sms.insure.po.Insure_manPo" resultClass="java.lang.Long">
select count(s.ID)
from S_V_STUDENTS s
left join T_INSURE_MAN i on s.ID = i.STUDENT_ID
left join T_CLASSES c on c.CLASS_CODE = s.CLASS_CODE
where 1 = 1
<dynamic>
<isNotEmpty prepend="and" property="id">
<![CDATA[ s.ID = #id:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="student_name" >
<![CDATA[ s.STUDENT_NAME like '%'||#student_name:VARCHAR#||'%' ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="class_code" >
<![CDATA[ s.CLASS_CODE = #class_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="academy_name" >
<![CDATA[ s.ACADEMY_NAME = #academy_name:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="academy_code" >
<![CDATA[ s.ACADEMY_CODE = #academy_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="class_name" >
<![CDATA[ s.CLASS_NAME like '%'||#class_name:VARCHAR#||'%' ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="state_code" >
<![CDATA[ s.STATE_CODE = #state_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="grade" >
<![CDATA[ s.GRADE = #grade:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="insure_state" >
<![CDATA[ nvl(i.INSURE_STATE, '-1') = #insure_state:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="insure_code" >
<![CDATA[ (case nvl(i.INSURE_CODE, '0') when '0' then 0 else 1 end ) = #insure_code:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="end_auditing_state" >
<![CDATA[ i.END_AUDITING_STATE in($end_auditing_state$) ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="receive_state" >
<![CDATA[ nvl(i.RECEIVE_STATE, '0') = #receive_state:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="ids" >
<![CDATA[ s.ID in($ids$) ]]>
</isNotEmpty>
<isNotEmpty property="teacher_id" prepend="and">
<![CDATA[ c.TEACHER_ID = #teacher_id:VARCHAR# ]]>
</isNotEmpty>
</dynamic>
<dynamic prepend="and" open="(" close=")">
<isNotEmpty prepend="or" property="is_boor" >
<![CDATA[ i.IS_BOOR = #is_boor:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="or" property="is_socialized" >
<![CDATA[ i.IS_SOCIALIZED = #is_socialized:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="or" property="is_outlander" >
<![CDATA[ i.IS_OUTLANDER = #is_outlander:VARCHAR# ]]>
</isNotEmpty>
<isNotEmpty prepend="or" property="is_nofee" >
<![CDATA[ i.IS_NOFEE = #is_nofee:VARCHAR# ]]>
</isNotEmpty>
</dynamic>
</select>
里面包括了分页\and\ or \多表联接查询\总条数等一些查询
最新推荐文章于 2023-05-11 00:51:38 发布