当前位置：文江博客话题详情

sql (oracle) 选择前 10 条记录，然后选择下 10 条，依此类推

发布于 2024-09-26 06:08:28 字数 145 浏览 1 评论 0原文

我想我可能只是缺少一些明显的语法，但是选择前 10 条记录、然后是下 10 条记录的 sql (oracle) 是什么？

我尝试使用 rownum 但似乎可以得到 rownum > X 和 rownum < Y 去工作。

LLCF

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我三岁 2024-10-03 06:08:28

只有一种相当复杂的方法来做到这一点，这对 Oracle 来说是一个真正的痛苦。他们应该只实现一个 LIMIT/OFFSET 子句...

在 where 子句选择行之后，rownum 被分配，因此 rownum 必须始终以 1 开头。 where rownum > ; x 将始终评估为 false。

另外，rownum 在排序完成之前被分配，因此 rownum 的顺序不会与您的 order by 所说的顺序相同。

您可以使用子选择来解决这两个问题：

 select a,b,c, rn from 
    ( select a,b,c, rownum rn from 
         ( select a,b,c from the_table where x = ? order by c)
      where rownum < Y)
  where rn > X

如果您不需要排序（但仅此而已），您可以简化为

 select a,b,c, rn from
     ( select a,b,c, rownum rn from the_table where rownum < Y )
   where rn > X

There is only a rather convoluted way to do this, which is a real pain with Oracle. They should just implement a LIMIT/OFFSET clause...

The rownum gets assigned after the row has been selected by the where clause, so that a rownum must always start with 1. where rownum > x will always evaluate to false.

Also, rownum gets assigned before sorting is done, so the rownum will not be in the same order as your order by says.

You can get around both problems with a subselect:

 select a,b,c, rn from 
    ( select a,b,c, rownum rn from 
         ( select a,b,c from the_table where x = ? order by c)
      where rownum < Y)
  where rn > X

If you do not need to sort (but only then), you can simplify to

 select a,b,c, rn from
     ( select a,b,c, rownum rn from the_table where rownum < Y )
   where rn > X

回复收藏 0 原文

梦途 2024-10-03 06:08:28

您可以使用 ROW_NUMBER() 函数...例如

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW < X

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW BETWEEN X AND Y

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW BETWEEN Y AND Z

You could use ROW_NUMBER() function... for example

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW < X

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW BETWEEN X AND Y

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW BETWEEN Y AND Z

回复收藏 0 原文