前n项(Top-n)查询
目标:与上述的简单限制结果集查询类似,但需要包含等值的关系。所以,查询可能会返回超过n行的数据。
有的称之为配额查询(quota-query)。
如下例:
SELECT * FROM person ORDER BY age ASC;
+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
| 7 | Hilda | 12 |
| 8 | Bill | 12 |
| 4 | Joe | 23 |
| 2 | Veronica | 23 |
| 3 | Michael | 27 |
| 9 | Marianne | 27 |
| 1 | Ben | 50 |
| 10 | Michelle | 50 |
| 5 | Irene | 77 |
| 6 | Vivian | 77 |
+----------+-------------+-----+
如果我们想找出3个最年轻的人(n=3),那么结果集应该如下:
+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
| 7 | Hilda | 12 |
| 8 | Bill | 12 |
| 4 | Joe | 23 |
| 2 | Veronica | 23 |
+----------+-------------+-----+
标准(Standard) | 对于SQL标准,主要有两种方法来获取值:
在SQL:2003中的一项重要添加就是附加的非核心OLAP(联机分析处理)特性。如果DBMS支持基本的OLAP(ID F611特性),那么top-n查询就可以使用RANK()这样的window函数了: SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) AS foo WHERE ranking <= 3
如果DBMS不支持基本的OLAP特性,那么可通过替代方法来解决top-n的问题。这种方法实在很慢,在大部分情况下都不会使用。
相关子查询方法:
SELECT * FROM person AS px WHERE ( SELECT COUNT(*) FROM person AS py WHERE py.age < px.age ) < 3 |
PostgreSQL | 支持慢速标准SQL。在实践中,需要使用PostgreSQL独有的表达式,以便获得较好的查询性能:
SELECT * FROM person WHERE ( age <= ( SELECT age FROM person ORDER BY age ASC LIMIT 1 OFFSET 2 -- 2= n-1 ) ) IS NOT FALSE
|
DB2 | 支持快速标准SQL |
MS SQL Server | 支持快速标准SQL
MSSQL 2000支持慢速标准SQL。在实践中,需要使用MSSQL独有的表达式,以便获得较好的查询性能:
SELECT TOP 3 WITH TIES * FROM person ORDER BY age ASC |
MySQL | 支持慢速标准SQL。在实践中,必须使用MySQL指定的解决方案,以便取得较好的查询性能:
SELECT * FROM person WHERE age <= COALESCE( --“COALESCE”关键字与括弧不要有空格 ( SELECT age FROM person ORDER BY age ASC LIMIT 1 OFFSET 2 -- 2 = n – 1 ), ( SELECT MAX (age) FROM person ) )
COALESCE调用中的第二个参数当表中的集合内容少于n时查询依然能正常工作 |
Oracle | 支持快速标准SQL。但由于Oracle不支持在子查询后加“AS”,所以查询需要简单修改:
SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) WHERE ranking <=3 |