等同于SQL Server TOP [英] Equivalents to SQL Server TOP

问题描述

在SQL Server中，TOP可用于返回查询中的前n个行。例如，可以使用

 SELECT TOP 100 * FROM users ORDER BY id 

返回注册网站的前100个人。 （这不一定是最好的方法，我只是用它作为例子）。

我的问题是 - 在其他数据库中等同于TOP，例如Oracle，MySQL，PostgreSQL等？如果没有等效的关键字，您可以推荐什么解决方法来实现相同的结果？

解决方案

$ c> 100 行：

MySQL 和 PostgreSQL ：

  SELECT * 
 FROM表
 ORDER BY 
 column 
 LIMIT 100 
  

Oracle ：

  SELECT * 
 FROM（
 SELECT t。* 
 FROM table 
 ORDER BY 
 column 
）
 WHERE rownum <= 100 
  

请注意，这里需要一个子查询。如果不添加子查询， ROWNUM 将以随机顺序选择第一个 10 行，然后按列。

要选择 100 和 300 ：

MySQL ：

  SELECT * 
 FROM TABLE 
 ORDER BY 
 column 
 LIMIT 100，200 
  

PostgreSQL ：

  SELECT * 
 FROM表
 ORDER BY 
列
 OFFSET 100 LIMIT 200 
  

Oracle ：

  SELECT * 
 FROM（
 SELECT t。*，ROW_NUMBER（）OVER（ORER BY column）AS rn 
 FROM table 
）
 WHERE rn> = 100 
 AND rownum <= 200 
  

请注意，简化它与外部查询中的 ROWNUM 100 AND 200 （而不是 rn BETWEEN 100 AND 200 ）将返回中没有！

将在 Oracle 中工作，但效率较低。

有关性能详情，请参阅我的博客中的文章： p>

• Oracle：ROW_NUMBER vs ROWNUM

In SQL Server, TOP may be used to return the first n number of rows in a query. For example,

SELECT TOP 100 * FROM users ORDER BY id

might be used to return the first 100 people that registered for a site. (This is not necessarily the best way, I am just using it as an example).

My question is - What is the equivalent to TOP in other databases, such as Oracle, MySQL, PostgreSQL, etc? If there is not an equivalent keyword, what workarounds can you recommend to achieve the same result?

解决方案

To select first 100 rows:

MySQL and PostgreSQL:

SELECT  *
FROM    Table
ORDER BY
        column
LIMIT 100

Oracle:

SELECT  *
FROM    (
        SELECT  t.*
        FROM    table
        ORDER BY
                column
        )
WHERE   rownum <= 100

Note that you need a subquery here. If you don't add a subquery, ROWNUM will select first 10 rows in random order and then sort them by column.

To select rows between 100 and 300:

MySQL:

SELECT  *
FROM    TABLE
ORDER BY
        column
LIMIT   100, 200

PostgreSQL:

SELECT  *
FROM    Table
ORDER BY
        column
OFFSET 100 LIMIT 200

Oracle:

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (ORER BY column) AS rn
        FROM    table
        )
WHERE   rn >= 100
        AND rownum <= 200

Note that an attempt to simplify it with ROWNUM BETWEEN 100 AND 200 (as opposed to rn BETWEEN 100 AND 200 in the outer query) will return nothing in Oracle!

RN BETWEEN 100 AND 200 will work in Oracle too but is less efficient.

See the article in my blog for performance details:

• Oracle: ROW_NUMBER vs ROWNUM

这篇关于等同于SQL Server TOP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！