各类数据库SELECT第11条-第20条记录的语句总结

发信人: Corps (最后一次机会了), 信区: Database
标  题: 各类数据库SELECT第11条-第20条记录的语句总结
发信站: 日月光华 (2004年05月27日11:36:32 星期四), 站内信件

如果有表news，其中字段有id, title, date(id为unique key)

目标：获得第11条到20条的纪录，按date排序
(这里的第11-20条记录是表示记录编号从1开始，也就是如果每次读取10条记录进行分页
，那么就是第二次取10条记录的意思)

数据库：
IBM DB2
MS SQL
Oracle PL/SQL
MySQL
还有JDBC环境下使用

特别感谢slowly, SEAH, Sunix, Jove。

--------------------------------------DB2------------------------------------
WITH temp1 AS
(SELECT * FROM news ORDER BY date FETCH FIRST 20 ROWS ONLY),
temp2 AS
(SELECT * FROM temp1 ORDER BY date DESC FETCH FIRST 10 ROWS ONLY)
SELECT * FROM temp2 ORDER BY date

用WITH生成两个临时表，把后一个临时表反向就可以了。
也可以用SEAH的方法（见下面说明*），
只是把top *换成FETCH FIRST clause。
另外还可以用ROW_NUMBER()生成一个编号，对编号进行处理。
没装DB2，所以上述语句没有经过测试。

-----------------------------------MS SQL-----------------------------------

SELECT t2.*
FROM (SELECT TOP 10 t1.id, t1.title, t1.date
       FROM (SELECT TOP 20 id, title, date
            FROM news ORDER BY date, id) t1
        ORDER BY t1.date DESC, t1.id DESC) t2
ORDER BY t2.date, t2.id

如果不要求最后结果是ORDER BY date ASC的话(就是说只要求结果集是正确的),上面的,
 就可以简化成:

SELECT TOP 10 t1.id, t1.title, t1.date
FROM (SELECT TOP 20 id, title, date
      FROM news ORDER BY date, id) t1
ORDER BY t1.date DESC, t1.id DESC
/*******************************************************/
*注：SEAH方法：
假设ORDER BY unique字段id，可以用如下语句:
SELECT TOP 10 id, title
FROM news
WHERE id NOT IN (SELECT TOP 10 id FROM news ORDER BY id)
ORDER BY id
/*****************************************************/

-----------------------------------Oracle------------------------------------

Oracle PL/SQL 中的：
使用Rownum:
Rownum是Oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二
行是2， 依此类推，这个伪字段可以用于限制查询返回的总行数。
Rownum不能以任何基表的名称作为前缀。

SELECT id, title, date
  FROM ( SELECT id, title, date, Rownum num
         FROM news WHERE num <= 20)
  WHERE num >= 11;
上面方法没有对时间排序,
或者,对时间排序:
SELECT id, title, date
   FROM (SELECT id, title, date, Rownum num
             FROM (SELECT * FROM news ORDER BY date))
   WHERE num BETWEEN 11 AND 20;

当然Oracle中还可以使用MINUS 方法进行操作：
(select id, title, date
  from (select id, title, date from news order by date)
  where rownum <= 20)
minus
(select id, title, date
  from (select id, title, date from news order by date)
  where rownum <= 10)
order by date;

不用minus也可以用下面的outer join实现同样的想法。

select *
from (select id, title, date, rownum num
  from (select id, title, date from news order by date)
  where rownum <= 20) a,
(select id, title, date, rownum num
  from (select id, title, date from news order by date)
  where rownum <= 10) b
where (a.num=b.num(+)) and (b.num is null);
后者在速度上比前者少快一点（几乎没差别）。

---------------------------------JDBC----------------------------------------

听说MySQL和PostgreSQL有LIMIT,类似TOP N的功能

另外,如果数据库支持JDBC 2的话,可以让直接跳到第11条,往下取10条数据
由于ResultSet是保持链接的,所以SELECT * FROM news并不会一次把数据全部读到本地

另外,如果使用JAVA开发还可以使用Hibernate (一个基于JDBC的O/R Mapping技术)
Query q = session.createQuery("from News as news");
q.setFirstResult(11);
q.setMaxResults(10);
List l = q.list();

它的分页机制比较smart:
如果当前数据库支持分页,则会gen出native的分页SQL(如使用top/limit/rownum)否则的
话,判断当前驱动是否是JDBC2或更高规范,如果是则使用前面说的绝对定位
再不行,就使用JDBC1方式的rs.next()略过无关记录,效率上也是可以接受的

----------------------------------MySQL--------------------------------------

使用MySQL的LIMIT字句使用，LIMIT [offset,] rows，这里需要指出的是offset是从0开
始计算的，所以语句可以写成：
SELECT id, title, date FROM news ORDER BY date LIMIT 10, 10

※ 来源:·日月光华 bbs.fudan.edu.cn·[FROM: 137.132.216.40]
※ 修改:·Corps 於 05月31日17:26:33 修改本文·[FROM: 10.100.166.93]