问题描述
我正在处理的一个相当复杂的 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:
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
).
这篇关于检索最大/最小记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!