SqlServer、Oracle中使用分页串.

SQLServer:

SELECT * FROM
     (
     SELECT TOP(PageSize) * FROM
     (
          SELECT TOP (PageSize * PageIndex) *
          FROM Articles
          ORDER BY id DESC
     )
     ORDER BY id ASC
)
ORDER BY id DESC

Oracle:

select * from (
 select rownum num,t1.*
  from table t1 where fast=0
) where num>=intPosition and num<=intEndPosition


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
其实sql server也可以这样。不过就是sql上写起来难一点，没有Oracle的方便。另外对于有order by的SQL传入进去的话，有时好像会有点问题。
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/pagercontrols.asp
官方是这样的
http://211.155.226.126:8090/UploadFile/500W分页录像.rar

没办法。我没网站。只好做个录像了。：）录像时占了不少资源。实际还要快一点。

原理可见《Hibernate分页查询原理解读》，也是泊来品。谁写的我忘记了。google一下吧。

public String getLimitString(String sql) {
  StringBuffer pagingSelect = new StringBuffer(100);
  pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
  pagingSelect.append(sql);
  pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
  return pagingSelect.toString();
}

Oracle采用嵌套3层的查询语句结合rownum来实现分页，这在Oracle上是最快的方式，如果只是一层或者两层的查询语句的rownum不能支持order by。

也就是根据这个写的。
实际代码

private void Page_Load(object sender, System.EventArgs e)
{
  if(!Page.IsPostBack)
  {
    TzhPager1.SelectCommand="select id,age,sex,memo from test"; 
    TzhPager1.ConnectionString="user id=test500;password=test500;data source=db01";
    TzhPager1.CurrentPageIndex = 0;
    TzhPager1.DataBind();
  }
}

就是扔一个任意合法的sql，生成一个新的SQL，再执行。没用存储过程。数据库里的表连索引都没建。：）

 

 

SQLSERVER==========================================================
SELECT * FROM
(SELECT TOP ItemsPerPage * FROM
(SELECT TOP ItemsPerPage*CurrentPageIndex * FROM
(SelectCommand) AS t0
ORDER BY SortField ASC) AS t1
ORDER BY SortField DESC) AS t2
ORDER BY SortField
以下是我的试验。不过好像很不方便
------

SELECT *
FROM (SELECT TOP 20 *
        FROM (SELECT TOP 20 *
                FROM (SELECT TOP 20 Employees.LastName, Employees.FirstName,
                              Orders.OrderID
                        FROM Employees INNER JOIN
                              Orders ON
                              Employees.EmployeeID = Orders.EmployeeID
                        ORDER BY Orders.OrderID) t0
                ORDER BY OrderID) T1
        ORDER BY OrderID DESC) DERIVEDTBL
ORDER BY OrderID

------------------------------------
SELECT *
FROM (SELECT TOP 20 *
        FROM (SELECT TOP 40 *
                FROM (SELECT TOP 20 Employees.LastName, Employees.FirstName,
                              SUM(Orders.Freight) AS Summ
                        FROM Employees INNER JOIN
                              Orders ON
                              Employees.EmployeeID = Orders.EmployeeID
                        GROUP BY Employees.LastName, Employees.FirstName
                        ORDER BY Employees.LastName, Employees.FirstName) t0
                ORDER BY LastName, FirstName) T1
        ORDER BY LastName, FirstName DESC) t2
ORDER BY LastName, FirstName