sql语句的查询
1.分页区间查询
select *
from (select rownum rw, a.*
from (
select * from eb_item t
) a
where rownum < 11) b
where b.rw > 0
/*
1. 先查询出全量的结果集
2. 以第一步的结果为结果集,以rownum小于结束行号为条件,结果是rownum和第一步的结果集
3. 以第二步的结果为结果集,以第二步查询出来的rownum的值大于开始行号为条件查询即可
*/
分页的使用
<pre name="code" class="html"><select id="selectItemByCondition" resultMap="BaseResultMap" parameterType="map">
select *
from (select rownum rw,a.*
from (select *
from eb_item t
<where>
<if test="brandId != null">
where t.brand_id = #{brandId}
</if>
<if test="auditStatus != null">
and t.audit_status = #{auditStatus}
</if>
<if test="brandId != null">
and t.item_name like '%${itemName}%'
</if>
</where>
<![CDATA[
) a
where rownum < #{startNum}) b
where b.rw > #{endNum}
]]>
</select>
统计页数
<!-- 当前查询条件下的数据条数 -->
<select id="selectItemByConditionCount" resultType="int" parameterType="map" >
select count(*)
from eb_item t
<where>
<if test="brandId != null">
where t.brand_id = #{brandId}
</if>
<if test="auditStatus != null">
and t.audit_status = #{auditStatus}
</if>
<if test="brandId != null">
and t.item_name like '%${itemName}%'
</if>
</where>
</select>