oracle top n sentences

最新推荐文章于 2024-04-19 07:25:26 发布

原创最新推荐文章于 2024-04-19 07:25:26 发布 · 877 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#11g #ORACLE #database #dba #分页

oracle 同时被 2 个专栏收录

56 篇文章

订阅专栏

PL/SQL

9 篇文章

订阅专栏

本文介绍了Oracle数据库中实现TOP-N查询的方法，包括基本的TOP-N查询、带条件的TOP-N查询及分页查询等，并展示了如何在涉及多表连接的情况下进行TOP-N查询。

Oracle has some sentences for top n

version oracle 10g 11g

TOP-N
SELECT * FROM (
  SELECT name, id
  FROM test
  ORDER BY id DESC
) WHERE rownum <= 5;
SELECT * FROM (
  SELECT name, id 
  FROM test
  WHERE type='0'
  ORDER BY id DESC 
) WHERE rownum <= 5;
分页
SELECT * FROM (
  SELECT * FROM (
    SELECT name, id,
      rownum AS rn
    FROM test
    WHERE type='0'
    ORDER BY id DESC
  ) WHERE rownum <= 20
) WHERE rn > 10;
Top-N with joins
SELECT * FROM (
  SELECT t.name , t.id,  e.dept
  FROM test t, em_ct e
  WHERE t.id = e.id
    AND t.type='0'
  ORDER BY t.idDESC
) WHERE rownum <= 5
/