sqlserver 分页 问题 怪
如题
我用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 ");
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
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 4 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