1、sql:分页查询
先分页查询获取id,再根据这些id 查询数据
select a.id, a.xm, a.xb, a.ksno, a.zkzh, a.bmxh, a.zjlx, a.zjno, a.xp, a.status, a.dizhi, a.xuexiao, a.banji, a.cscode, a.
fenpei_cscode, a.fenzu_code, a.fenzu_num, a.fenzu_datetime, a.tijiandian_id, a.kaoshi_code, a.
updatetime, a.bak1, a.bak2, a.csrq, a.mzdm, a.wyyzdm, a.kslbdm, a.zzmmdm, a.bylbdm, a.xxdm, a.hkszddm,a.print_status, a.print_datetime, a.zhiwen_status, a.zhiwen_datetime,
a.querenbiao_status, a.querenbiao_datetime, a.zhujian_status, a.zhujian_datetime, a.zhiwenqueren_way,b.zong_status,d.id zdyj_id,d.keyNum,d.valueStr
from t_bmk a,tj_huizong b,t_bmk_zdyj c,t_zdyj d where a.id=b.ks_id and a.id=c.ks_id and c.zdyj_id=d.id and a.id in(
select
id
from
(
select a.id,row_number() over
(
order by ksno
) num
from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id)) as ta where ta.num >1 and ta.num<30);
sqlserver 存储过程
ALTER PROCEDURE [dbo].[tiaoxingma_getAllByPage]
(
@txm_ksno AS varchar(50) ,
@txm_zjno AS varchar(50) ,
@txm_bmxh AS varchar(50) ,
@txm_xm AS varchar(50) ,
@txm_xuexiao AS varchar(200) ,
@txm_xb AS varchar(1) ,
@txm_printStatus AS varchar(10) ,
@txm_qsbjbh AS varchar(50) ,
@txm_zzbjbh AS varchar(50) ,
@txm_qsbmxh AS varchar(50) ,
@txm_zzbmxh AS varchar(50) ,
@startnum AS int ,
@endnum AS int
)
AS
BEGIN
declare @sql nvarchar(2000)
set @sql='
select a.id, a.xm, a.xb, a.ksno, a.zkzh, a.bmxh, a.zjlx, a.zjno, a.xp, a.status, a.dizhi, a.xuexiao, a.banji, a.cscode, a.
fenpei_cscode, a.fenzu_code, a.fenzu_num, a.fenzu_datetime, a.tijiandian_id, a.kaoshi_code, a.
updatetime, a.bak1, a.bak2, a.csrq, a.mzdm, a.wyyzdm, a.kslbdm, a.zzmmdm, a.bylbdm, a.xxdm, a.hkszddm,a.print_status, a.print_datetime, a.zhiwen_status, a.zhiwen_datetime,
a.querenbiao_status, a.querenbiao_datetime, a.zhujian_status, a.zhujian_datetime, a.zhiwenqueren_way,b.zong_status,d.id zdyj_id,d.keyNum,d.valueStr
from t_bmk a,tj_huizong b,t_bmk_zdyj c,t_zdyj d where a.id=b.ks_id and a.id=c.ks_id and c.zdyj_id=d.id and a.id in(
select
id
from
(
select a.id,row_number() over
(
order by ksno
) num
from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id) ';
IF(@txm_ksno is not null and @txm_ksno!='')
BEGIN
set @sql=@sql+' and a.ksno like ''%'+@txm_ksno+'%'' '
END
IF(@txm_zjno is not null and @txm_zjno!='')
BEGIN
set @sql=@sql+' and a.zjno like ''%'+@txm_zjno+'%'' '
END
IF(@txm_bmxh is not null and @txm_bmxh!='')
BEGIN
set @sql=@sql+' and a.bmxh like ''%'+@txm_bmxh+'%'' '
END
IF(@txm_xm is not null and @txm_xm!='')
BEGIN
set @sql=@sql+' and a.xm like ''%'+@txm_xm+'%'' '
END
IF(@txm_xuexiao is not null and @txm_xuexiao!='')
BEGIN
set @sql=@sql+' and a.xuexiao like ''%'+@txm_xuexiao+'%'' '
END
IF(@txm_xb is not null and @txm_xb!='')
BEGIN
set @sql=@sql+' and a.xb ='+@txm_xb
END
IF(@txm_printStatus is not null and @txm_printStatus!='')
BEGIN
set @sql=@sql+' and a.print_status ='''+@txm_printStatus+''''
END
IF(@txm_qsbjbh is not null and @txm_qsbjbh!='')
BEGIN
set @sql=@sql+' and a.banji >= '+@txm_qsbjbh
END
IF(@txm_zzbjbh is not null and @txm_zzbjbh!='')
BEGIN
set @sql=@sql+' and a.banji <='+@txm_zzbjbh
END
IF(@txm_qsbmxh is not null and @txm_qsbmxh!='')
BEGIN
set @sql=@sql+' and a.bmxh >= '''+@txm_qsbmxh+''''
END
IF(@txm_zzbmxh is not null and @txm_zzbmxh!='')
BEGIN
set @sql=@sql+' and a.bmxh <= '''+@txm_zzbmxh+''''
END
set @sql=@sql+') as ta where ta.num > '+CONVERT(varchar(50),@startnum) + ' and ta.num <='+CONVERT(varchar(50), @endnum) +')'
exec sp_executesql @sql
END
2、查询数据总数:
sql
select count(1)
from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id)
sqlserver存储过程:
ALTER PROCEDURE [dbo].[tiaoxingma_getAllByPage_count]
(
@txm_ksno AS varchar(50) ,
@txm_zjno AS varchar(50) ,
@txm_bmxh AS varchar(50) ,
@txm_xm AS varchar(50) ,
@txm_xuexiao AS varchar(200) ,
@txm_xb AS varchar(10) ,
@txm_printStatus AS varchar(10) ,
@txm_qsbjbh AS varchar(50) ,
@txm_zzbjbh AS varchar(50) ,
@txm_qsbmxh AS varchar(50) ,
@txm_zzbmxh AS varchar(50)
)
AS
BEGIN
declare @sql nvarchar(2000)
set @sql='
select count(1)
from t_bmk a,tj_huizong b where a.id=b.ks_id and exists(select 1 from t_bmk_zdyj where ks_id=a.id) ';
IF(@txm_ksno is not null and @txm_ksno!='')
BEGIN
set @sql=@sql+' and a.ksno like ''%'+@txm_ksno+'%'' '
END
IF(@txm_zjno is not null and @txm_zjno!='')
BEGIN
set @sql=@sql+' and a.zjno like ''%'+@txm_zjno+'%'' '
END
IF(@txm_bmxh is not null and @txm_bmxh!='')
BEGIN
set @sql=@sql+' and a.bmxh like ''%'+@txm_bmxh+'%'' '
END
IF(@txm_xm is not null and @txm_xm!='')
BEGIN
set @sql=@sql+' and a.xm like ''%'+@txm_xm+'%'' '
END
IF(@txm_xuexiao is not null and @txm_xuexiao!='')
BEGIN
set @sql=@sql+' and a.xuexiao like ''%'+@txm_xuexiao+'%'' '
END
IF(@txm_xb is not null and @txm_xb!='')
BEGIN
set @sql=@sql+' and a.xb ='+@txm_xb
END
IF(@txm_printStatus is not null and @txm_printStatus!='')
BEGIN
set @sql=@sql+' and a.print_status ='+@txm_printStatus
END
IF(@txm_qsbjbh is not null and @txm_qsbjbh!='')
BEGIN
set @sql=@sql+' and a.banji >= '+@txm_qsbjbh
END
IF(@txm_zzbjbh is not null and @txm_zzbjbh!='')
BEGIN
set @sql=@sql+' and a.banji <='+@txm_zzbjbh
END
IF(@txm_qsbmxh is not null and @txm_qsbmxh!='')
BEGIN
set @sql=@sql+' and a.bmxh >='''+@txm_qsbmxh+''''
END
IF(@txm_zzbmxh is not null and @txm_zzbmxh!='')
BEGIN
set @sql=@sql+' and a.bmxh <= '''+@txm_zzbmxh+''''
END
exec sp_executesql @sql
END
3、mybatis xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zr.dao.TxmMapper">
<resultMap id="BaseResultMap" type="com.zr.model.KaoSheng">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="xm" jdbcType="NVARCHAR" property="xm"/>
<result column="xb" jdbcType="INTEGER" property="xb"/>
<result column="ksno" jdbcType="VARCHAR" property="ksno"/>
<result column="zkzh" jdbcType="VARCHAR" property="zkzh"/>
<result column="bmxh" jdbcType="VARCHAR" property="bmxh"/>
<result column="zjlx" jdbcType="VARCHAR" property="zjlx"/>
<result column="zjno" jdbcType="VARCHAR" property="zjno"/>
<result column="xp" jdbcType="VARCHAR" property="xp"/>
<result column="status" jdbcType="INTEGER" property="status"/>
<result column="dizhi" jdbcType="VARCHAR" property="dizhi"/>
<result column="xuexiao" jdbcType="VARCHAR" property="xuexiao"/>
<result column="banji" jdbcType="VARCHAR" property="banji"/>
<result column="cscode" jdbcType="VARCHAR" property="cscode"/>
<result column="fenpei_cscode" jdbcType="VARCHAR" property="fenpeiCscode"/>
<result column="fenzu_code" jdbcType="VARCHAR" property="fenzuCode"/>
<result column="fenzu_num" jdbcType="INTEGER" property="fenzuNum"/>
<result column="fenzu_datetime" jdbcType="TIMESTAMP" property="fenzuDatetime"/>
<result column="tijiandian_id" jdbcType="BIGINT" property="tijiandianId"/>
<result column="kaoshi_code" jdbcType="VARCHAR" property="kaoshiCode"/>
<result column="updatetime" jdbcType="TIMESTAMP" property="updatetime"/>
<result column="bak1" jdbcType="BIGINT" property="bak1"/>
<result column="bak2" jdbcType="VARCHAR" property="bak2"/>
<result column="csrq" jdbcType="VARCHAR" property="csrq"/>
<result column="mzdm" jdbcType="VARCHAR" property="mzdm"/>
<result column="wyyzdm" jdbcType="VARCHAR" property="wyyzdm"/>
<result column="kslbdm" jdbcType="VARCHAR" property="kslbdm"/>
<result column="zzmmdm" jdbcType="VARCHAR" property="zzmmdm"/>
<result column="bylbdm" jdbcType="VARCHAR" property="bylbdm"/>
<result column="xxdm" jdbcType="VARCHAR" property="xxdm"/>
<result column="hkszddm" jdbcType="VARCHAR" property="hkszddm"/>
<result column="print_status" jdbcType="VARCHAR" property="printStatus"/>
<result column="print_datetime" jdbcType="TIMESTAMP" property="printDatetime"/>
<result column="zhiwen_status" jdbcType="VARCHAR" property="zhiwenStatus"/>
<result column="zhiwen_datetime" jdbcType="TIMESTAMP" property="zhiwenDatetime"/>
<result column="querenbiao_status" jdbcType="VARCHAR" property="querenbiaoStatus"/>
<result column="querenbiao_datetime" jdbcType="TIMESTAMP" property="querenbiaoDatetime"/>
<result column="zhujian_status" jdbcType="VARCHAR" property="zhujianStatus"/>
<result column="zhujian_datetime" jdbcType="TIMESTAMP" property="zhujianDatetime"/>
<result column="zhiwenqueren_way" jdbcType="VARCHAR" property="zhiwenquerenWay"/>
<result column="zong_status" jdbcType="VARCHAR" property="zongStatus"/>
<result column="querenbiao_frequency" jdbcType="INTEGER" property="querenbiaoFrequency" />
<collection property="zdyjs" ofType="com.zr.model.Zdyj">
<id column="zdyj_id" jdbcType="INTEGER" property="id"/>
<result column="keyNum" jdbcType="VARCHAR" property="keynum"/>
<result column="valueStr" jdbcType="VARCHAR" property="valuestr"/>
<result column="type" jdbcType="INTEGER" property="type"/>
</collection>
</resultMap>
<select id="tiaoxingma_getAllByPage" statementType="CALLABLE" resultMap="BaseResultMap">
{CALL tiaoxingma_getAllByPage(#{ksno},#{zjno},#{bmxh},#{xm},#{xuexiao},#{xb},#{printStatus},#{qsbjbh},#{zzbjbh},#{qsbmxh},#{zzbmxh},#{startnum},#{endnum})}
</select>
<select id="tiaoxingma_getAllByPage_count" resultType="INTEGER" statementType="CALLABLE">
{CALL tiaoxingma_getAllByPage_count(#{ksno},#{zjno},#{bmxh},#{xm},#{xuexiao},#{xb},#{printStatus},#{qsbjbh},#{zzbjbh},#{qsbmxh},#{zzbmxh})}
</select>
</mapper>