# Sql Server 分页查询

利用offset/fetch next

实例SQL

 SELECT B.* FROM 
   (SELECT WR.WONO,WR.WORKORDER,WR.COMPONENT,WR.MATERIALCODE,WR.PRODUCT,WR.SPEC,
    WR.UNIT,WR.INVENTORYQUANTITY,WR.WAREHOUSEGUID,WR.STORAGEGUID,WR.ORDERNO,
    WR.MODIFIEDTIME,SI.Storage AS STORAGENAME,WI.WAREHOUSE AS WAREHOUSENAME
    FROM WareHouse_Real WR
    LEFT JOIN Storage_Info SI ON SI.Id=WR.STORAGEGUID
    LEFT JOIN WareHouse_Info WI ON WI.ID=WR.WAREHOUSEGUID WHERE 1=1) AS B  ORDER BY 1 OFFSET 31 ROWNEXT 15 ROWS ONLY;
1
2
3
4
5
6
7

offset A rows,将前A条记录舍去,fetch next B rows only,向后在读取B条数据

引用:https://blog.csdn.net/weixin_37610397/article/details/80892426

# Oracle 分页查询

 DynamicParameters pars = new DynamicParameters();
 StringBuilder sql = new StringBuilder();
 sql.Append(@"SELECT * FROM (SELECT ROWNUM RN2,TEMP1.* FROM(select rownum RN1, sl.guid,sl.storagename,sl.storagecode,storagetype,
                                sl.warehouseguid,decode(sl.status,'0','无效','1','有效','无')Status,
                                sl.remark,sl.creater,sl.createdate,sl.lastupdby,sl.lastupddate,
                                whl.warehousename,
                                whl.warehousecode,
                                ui1.LoginUser as CreaterName,
                                ui2.LoginUser as LastUpdByName
                                from H2_StorageList sl
                                left join H2_UserInfo ui1 on ui1.UserGuid = sl.Creater
                                left join H2_UserInfo ui2 on ui2.UserGuid = sl.LastUpdBy
                                join h2_warehouselist whl on whl.guid = sl.warehouseguid) TEMP1 WHERE 1=1 ");
 if (!string.IsNullOrEmpty(qry.StorageName))
 {
     sql.Append(" AND TEMP1.STORAGENAME=:Storagename");
     pars.AddDynamicParams(new { Storagename = qry.StorageName });
 }
 if (!string.IsNullOrEmpty(qry.WareHouseGuid))
 {
     sql.Append(" AND TEMP1.WAREHOUSEGUID=:Warehouseguid");
     pars.AddDynamicParams(new { Warehouseguid = qry.WareHouseGuid });
 }
 if (!string.IsNullOrEmpty(qry.Status))
 {
     sql.Append(" AND TEMP1.STATUS=:Status");
     pars.AddDynamicParams(new { Status = qry.Status });
 }
 sql.Append("  )TEMP2 WHERE TEMP2.RN2 BETWEEN ((:CurPage - 1) * :PageSize + 1) AND (:CurPage * :PageSize)");
 pars.AddDynamicParams(new { CurPage = curPager, PageSize = pagerSize });
 list = (await conn.QueryAsync<VMStorageListInfo>(sql.ToString(), pars)).ToList();
 result.Data = list;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Last Updated: 11/11/2020, 11:10:23 AM