说说oracle分页的sql语句
说说oracle分页的sql语句,分排序和不排序两种。
当结果集不需要进行排序时,每页显示条数为:rowPerPage,当前页数为:currentPage。
1、 相对来说,这种查询速度会快一些,因为当currentPage小时,嵌套语句查询的结果集小。但当currentPage 变大时,查询速度会慢慢变慢。当结果集很大时,查询第一页跟最后一页的速度会有明显变化。(倾向用这种!)
select * from(
select rownum r, field1,field2 from table_name where rownum <= currentPage * rowPerPage
)
where r > (currentPage-1) * rowPerPage
2、相对来说,这种查询速度会慢一些,无论当currentPage大小,嵌套语句查询的结果集都是一样多的,都是整个结果集。但是,当结果集很大时,查询第一页跟最后一页的速度不会有明显变化。
select * from(
select rownum r, field1,field2 from table_name
)
where r > (currentPage-1) * rowPerPage and r <= currentPage * rowPerPage
当需要进行排序时,以第一种方式进行示例如下:
select * from(
select rownum r, a.* from (
select field1,field2 from table_name order by field1
) a where rownum <= currentPage * rowPerPage
) where r > (currentPage-1) * rowPerPage
还有这个,也是很好用的:
select
*
from
(
select
a.*,rownum row_num
from
(
select
*
from
tabelTest t
order
by
t.id ) a
) b
where
b.row_num
between
(currentPage-1)*pagesize
and
currentPage*pagesize
// public string List(int page, int rows, string sort, string order, string DataBase) { string where = Common.HqlQeuryHelp.GetWhere(typeof(Model.TIPTOP.CPF_FILE), "CPF01,CPF02,CPF29,CPF31,CPF70,CPF35"); //string OracelStr = "select cpf01,cpf02,cpf29,cpf31,cpk02,cqi02,cqi06,cqi09,cqi06-cqi09 as diff,cpf70,cpf35,rownum as rowi from "; // OracelStr += "tekvertps.cqi_file,tekvertps.cpf_file it,tekvertps.cpk_file where cpf31=cpk01 and cpf01=cqi01 and cpf35 is null and rownum<100"; string OracelStr = "select cpf01,cpf02,cpf29,cpf31,cpk02,cqi02,cqi06,cqi09,cqi06-cqi09 as diff,cpf70,cpf35,rownum as r from "; OracelStr += "(select cpf01,cpf02,cpf29,cpf31,cpk02,cqi02,cqi06,cqi09,cqi06-cqi09 as diff,cpf70,cpf35,rownum row_num from "; OracelStr += "tekvertps.cpf_file, tekvertps.cqi_file,tekvertps.cpk_file where cpf31=cpk01 and cpf01=cqi01 and cpf35 is null order by cpf01 ) it "; OracelStr += "where it.row_num between " + (page-1)*rows +" and " + page*rows; if (!string.IsNullOrEmpty(where)) { OracelStr += " and " + where; } OracleDataReader dr = OA.Common.OracleHelper.ExecuteReader(OA.Common.OracleHelper.ConnectionStringTipTopTPS, CommandType.Text, OracelStr, null); var cpfbll = new BLL.TIPTOP.CPF_FILE(DataBase); var count = cpfbll.GetTextCount(where); List<object> listView = new List<object>(); foreach (var i in dr) { listView.Add(new { CPF01 = dr["CPF01"].ToString(), CPF02 = dr["CPF02"].ToString(), CPF29 = dr["CPF29"].ToString(), CPF31 = dr["CPF31"].ToString(), CPK02 = dr["CPK02"].ToString(), CQI02 = dr["CQI02"].ToString(), CQI06 = dr["CQI06"].ToString(), CQI09 = dr["CQI09"].ToString(), DIFF = dr["DIFF"].ToString(), CPF70 = Formate_Date((DateTime)dr["CPF70"]), CPF35 = dr["CPF35"].ToString() }); } var ret = new { total = count, rows = listView }; return ToJsonString(ret); }
前台(MVC easyui-datagrid):
<div region="center" border="false"> <table class="easyui-datagrid" id="details" data-options="fit:true,singleSelect:true, fitColumns:true,pageSize:10, pagination:true,rownumbers:true,border:true,url:serverUrl.list,view: detailview,detailFormatter:detailformatter,onExpandRow:rowExpand"> <thead> <tr> <th data-options="field:'CPF01',sortable:true,width:100">員工代號</th> <th data-options="field:'CPF02',sortable:true,width:100">姓名</th> <th data-options="field:'CPF29',sortable:true,width:100">部門別</th> <th data-options="field:'CPF31',sortable:true,width:100">職稱代號</th> <th data-options="field:'CPK02',sortable:true,width:100">職稱</th> <th data-options="field:'CQI02',sortable:true,width:100">年度</th> <th data-options="field:'CQI06',sortable:true,width:100">特休天數</th> <th data-options="field:'CQI09',sortable:true,width:100">已休天數</th> <th data-options="field:'DIFF',sortable:true,width:100">未休天數</th> <th data-options="field:'CQH071S1',sortable:true,width:100">事假天數</th> <th data-options="field:'CQH071S2',sortable:true,width:100">病假天數</th> <th data-options="field:'CPF70',sortable:true,width:100">到職日期</th> <th data-options="field:'CPF35',sortable:true,width:100">離職日期</th> </tr> </thead> </table> </div>