检索最大/最小记录 [英] Retrieve maximal / minimal record

问题描述

我正在处理的一个相当复杂的 SQL 查询让我想到了 (ANSI) SQL 的局限性:

A rather complicated SQL query I was working on got me thinking about a limitation of (ANSI) SQL:

有没有一种方法可以检索任意排序最大或最小的记录?

Is there a way to retrieve a record that is maximal or minimal with respect to an arbitrary ordering?

换句话说:

给出这样的查询:

SELECT * FROM mytable WHERE <various conditions> ORDER BY <order clause>

是否可以编写仅返回第一行的查询(可能通过将 order 子句转换为其他内容)?

is it possible to write a query that returns only the first row (possibly by transforming the order clause into something else)?

我知道您可以使用 LIMIT (MySQL)/ROWNUM (Oracle) 或类似方法来执行此操作，但这不是标准 SQL.

I know you can do this using LIMIT (MySQL) / ROWNUM (Oracle) or similar, but that's not standard SQL.

我也知道您可以通过在子查询中获取您感兴趣的最大值/最小值(使用 MIN()/MAX())来完成此操作，然后将该结果用作主 SELECT 中的标准，即:

I also know you can do this by fetching the max/min value you are interested in in a subquery (using MIN()/MAX()), then use that result as a criterion in your main SELECT, i.e.:

SELECT * FROM mytable WHERE <various conditions> AND myMaxColumn=(
  SELECT MAX(myMaxColumn) FROM mytable WHERE <various conditions>
)

但这仅适用于我想按单列排序的情况.我认为没有办法将其推广到多列(除了嵌套上述解决方案，但这意味着按 n 列排序时有 2^n 个 SELECT).

But that only works if I want to sort by a single column. I see no way to generalize this to multiple columns (other than nesting the above solution, but that would mean 2^n SELECTs when ordering by n coluns).

那么在标准 SQL 中还有比嵌套多个子选择更好的方法吗?

So is there a better way in standard SQL than nesting multiple subselects?

创建 SQL 查询以检索最近的记录.但是，那里的答案建议使用 LIMIT &朋友，或者如上所述使用带有 MAX() 的子查询，这两者都不是我的问题的解决方案.

A related question is asked in Create a SQL query to retrieve most recent records. However, the answers there suggest either using LIMIT & friends, or to use a subquery with a MAX() as explained above, both of which are not solutions to my question.

推荐答案

SQL:2003 定义了窗口函数的概念，其中之一:

SQL:2003 defines concept of window functions, one of which:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY col1, col2, col3) AS rn
        FROM    mytable
        ) q
WHERE   rn = 1

将返回给您第一条记录.

will return you this first record.

目前，SQL Server、Oracle 和 2009 年 7 月 1 日 支持 PostgreSQL 8.4代码>

As for now, it's supported by SQL Server, Oracle and since Jul 01, 2009, by PostgreSQL 8.4

但是请注意，Oracle 中的 ROW_NUMBER() 比限制记录的本地方式(即 ROWNUM)效率低.

Note, however, that ROW_NUMBER() in Oracle is less efficient than the native way to limit records (i. e. ROWNUM).

请参阅我博客中的这篇文章以进行性能比较:

See this article in my blog for performance comparison:

• 甲骨文:ROW_NUMBER对比 ROWNUM

SQL:2008 提供了另一个子句来做到这一点:

SQL:2008 offers another clause to do this:

SELECT  *
FROM    mytable
ORDER BY
        col1, col2, col3
FETCH FIRST 1 ROW ONLY

，但就目前而言，只有 DB2 支持这种确切的语法(AFAIK).

, but as for now, this exact syntax is supported by DB2 only (AFAIK).

这篇关于检索最大/最小记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！