如题
我用sql server 分页写法
sqlstr2.append(" select TOP " + pageSize + " t.* from (" + sqlstr1.toString() + ") t " )
.append(" where " + sql + " and t.zid not in ")
.append(" ( select TOP " + (pageNo-1)*pageSize + " t.zid from (" + sqlstr1.toString() + ") t " )
.append(" where " + sql + " order by t.zid ) order by t.zid ");
通的是这种分页的写法
sql条件原来是 一堆条件,没问题
----------------------------
现在增加了一个条件 是用 t.zid 来判断的
t.zid in(23,24,25) 等,这样一个条件
我的写法是 原来的sql=原来的条件 or 上 新的条件
结果 分页的时候有重复。
更怪的是 :若 只有一页,也就是 在 后台运行时 把 4 换成40 就 显示5条,
若分页 ,比如 设置4 第二页的时候又 出现3条 共8条
总有重复的,望高人看看这个 怪异的 事情
------最佳解决方案--------------------
建议你先在SQL的界面里执行下完整的分页代码,要包括条件等,看看出来的结果是否正确。
我觉得问题出在OR这个问题上很大。
------其他解决方案--------------------
兄弟 你表达不是很清楚啊 你贴出的分页代码也没有问题啊 说这么多 不如把你有问题的分页代码贴出来
------其他解决方案--------------------
select TOP 4 t.* from ( select pi.companyName as cname, z.ID as zid,z.name as zname,broough,street,buildCount1,buildCount2,houseCount1,houseCount2, totalArea1,totalArea2,occupyArea,businessArea,publicArea,rcommitteID,developerID,dm.dept_name as pname,completeTime,bankID,di.name as dname,address,publicAddress,businessAddress,Remark, z.spare1,di2.name as mname,z.spare2 ,z.spare3,z.spare4,z.spare5,bankName,bankAccountNum from zone z join property_info pi on (Left(z.spare2,1) = 'P' and convert(int,replace(z.spare2,'P','')) = pi.ID) left join disc_info di2 on (z.spare1 = di2.disc_id and di2.type='B') left join disc_info di on (z.bankID = di.disc_id and di.type='C') left join department dm on (convert(int,replace(dm.dept_id,'k','')) = z.developerID and parentdept='KFS') union all select ci.c_name as cname, z.ID as zid,z.name as zname,broough,street,buildCount1,buildCount2,houseCount1,houseCount2, totalArea1,totalArea2,occupyArea,businessArea,publicArea,rcommitteID,developerID,dm.dept_name as pname,completeTime,bankID,di.name as dname,address,publicAddress,businessAddress,Remark, z.spare1,di2.name as mname,z.spare2 ,z.spare3,z.spare4,z.spare5,bankName,bankAccountNum from zone z join community_info ci on (Left(z.spare2,1) = 'C' and convert(int,replace(z.spare2,'C','')) = ci.ID) left join disc_info di2 on (z.spare1 = di2.disc_id and di2.type='B') left join disc_info di on (z.bankID = di.disc_id and di.type='C') left join department dm on (convert(int,replace(dm.dept_id,'k','')) = z.developerID and parentdept='KFS') union all select ci.name as cname, z.ID as zid,z.name as zname,broough,street,buildCount1,buildCount2,houseCount1,houseCount2, totalArea1,totalArea2,occupyArea,businessArea,publicArea,rcommitteID,developerID,dm.dept_name as pname,completeTime,bankID,di.name as dname,address,publicAddress,businessAddress,Remark, z.spare1,di2.name as mname,z.spare2 ,z.spare3,z.spare4,z.spare5,bankName,bankAccountNum from zone z left join zone_ext ci on (LEN(z.spare2) = 0) left join disc_info di2 on (z.spare1 = di2.disc_id and di2.type='B') left join disc_info di on (z.bankID = di.disc_id and di.type='C') left join department dm on (convert(int,replace(dm.dept_id,'k','')) = z.developerID and parentdept='KFS') where ( (LEN(z.spare2) = 0) or z.spare2 in ('Z', 'S')) ) t where ( t.spare2 is not null and t.spare2 = 'P248') or ( t.spare2 is not null and t.spare2 <> 'P248' and t.spare2 = 'S' and t.zid in (23,35)) and t.zid not in ( select TOP 0 t.zid from ( select pi.companyName as cname, z.ID as zid,z.name as zname,broough,street,buildCount1,buildCount2,houseCount1,houseCount2, totalArea1,totalArea2,occupyArea,businessArea,publicArea,rcommitteID,developerID,dm.dept_name as pname,completeTime,bankID,di.name as dname,address,publicAddress,businessAddress,Remark, z.spare1,di2.name as mname,z.spare2 ,z.spare3,z.spare4,z.spare5,bankName,bankAccountNum from zone z join property_info pi on (Left(z.spare2,1) = 'P' and convert(int,replace(z.spare2,'P','')) = pi.ID) left join disc_info di2 on (z.spare1 = di2.disc_id and di2.type='B') left join disc_info di on (z.bankID = di.disc_id and di.type='C') left join department dm on (convert(int,replace(dm.dept_id,'k','')) = z.developerID and parentdept='KFS') union all select ci.c_name as cname, z.ID as zid,z.name as zname,broough,street,buildCount1,buildCount2,houseCount1,houseCount2, totalArea1,totalArea2,occupyArea,businessArea,publicArea,rcommitteID,developerID,dm.dept_name as pname,completeTime,bankID,di.name as dname,address,publicAddress,businessAddress,Remark, z.spare1,di2.name as mname,z.spare2 ,z.spare3,z.spare4,z.spare5,bankName,bankAccountNum from zone z join community_info ci on (Left(z.spare2,1) = 'C' and convert(int,replace(z.spare2,'C','')) = ci.ID) left join disc_info di2 on (z.spare1 = di2.disc_id and di2.type='B') left join disc_info di on (z.bankID = di.disc_id and di.type='C') left join department dm on (convert(int,replace(dm.dept_id,'k','')) = z.developerID and parentdept='KFS') union all select ci.name as cname, z.ID as zid,z.name as zname,broough,street,buildCount1,buildCount2,houseCount1,houseCount2, totalArea1,totalArea2,occupyArea,businessArea,publicArea,rcommitteID,developerID,dm.dept_name as pname,completeTime,bankID,di.name as dname,address,publicAddress,businessAddress,Remark, z.spare1,di2.name as mname,z.spare2 ,z.spare3,z.spare4,z.spare5,bankName,bankAccountNum from zone z left join zone_ext ci on (LEN(z.spare2) = 0) left join disc_info di2 on (z.spare1 = di2.disc_id and di2.type='B') left join disc_info di on (z.bankID = di.disc_id and di.type='C') left join department dm on (convert(int,replace(dm.dept_id,'k','')) = z.developerID and parentdept='KFS') where ( (LEN(z.spare2) = 0) or z.spare2 in ('Z', 'S')) ) t where ( t.spare2 is not null and t.spare2 = 'P248') or ( t.spare2 is not null and t.spare2 <> 'P248' and t.spare2 = 'S' and t.zid in (23,35)) order by t.zid ) order by t.zid