如何获得Nth大项目

Question

我将使用以下SQL语句从users表中获取第11位老年人

select MAX(age) 
from ( select * 
       from (select * 
             from users 
             order by age asc)  
       where rownum <12)

是否有一个简单有效的查询来获取第11位老年人的全部信息？

使用Oracle 11G

Answer 1

WITH AgeOrderedPersons AS (
    SELECT usr.*
          ,ROW_NUMBER() OVER (ORDER BY Age) AS Number
    FROM Users usr
)
SELECT *
FROM AgeOrderedPersons
WHERE Number = 11

如果您希望所有年龄相同的用户使用DENSE_RANK()而不是ROW_NUMBER()

Answer 2

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE users ( age, x ) AS
          SELECT 20, 1 FROM DUAL
UNION ALL SELECT 80, 2 FROM DUAL
UNION ALL SELECT 47, 3 FROM DUAL
UNION ALL SELECT 33, 4 FROM DUAL
UNION ALL SELECT 24, 5 FROM DUAL
UNION ALL SELECT 7, 6 FROM DUAL
UNION ALL SELECT 102, 7 FROM DUAL
UNION ALL SELECT 99, 8 FROM DUAL
UNION ALL SELECT 90, 9 FROM DUAL
UNION ALL SELECT 28, 10 FROM DUAL
UNION ALL SELECT 46, 11 FROM DUAL
UNION ALL SELECT 54, 12 FROM DUAL
UNION ALL SELECT 67, 13 FROM DUAL
UNION ALL SELECT 17, 14 FROM DUAL
UNION ALL SELECT 34, 15 FROM DUAL
UNION ALL SELECT 32, 16 FROM DUAL
UNION ALL SELECT 39, 17 FROM DUAL
UNION ALL SELECT 26, 18 FROM DUAL
UNION ALL SELECT 15, 19 FROM DUAL
UNION ALL SELECT 12, 20 FROM DUAL;

查询1

SELECT DISTINCT
       NTH_VALUE( age, 11 ) IGNORE NULLS OVER ( ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS age,
       NTH_VALUE( x,   11 ) IGNORE NULLS OVER ( ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS x
FROM   users

结果

| AGE |  X |
|-----|----|
|  34 | 15 |

查询2

在带有ROW_NUMBER()的语句中包含一个排序子句

WITH ranked AS (
  SELECT u.*,
         ROW_NUMBER() OVER ( ORDER BY age ) AS rn
  FROM   users u
  ORDER BY age
)
SELECT age, x
FROM   ranked
WHERE  rn = 11

结果

| AGE |  X |
|-----|----|
|  34 | 15 |

查询3

WITH ordered AS (
  SELECT *
  FROM   users
  ORDER BY age
),
ranked AS (
  SELECT o.*,
         ROWNUM AS rn
  FROM   ordered o
  WHERE  ROWNUM <= 11
)
SELECT age, x
FROM   ranked
WHERE  rn = 11

结果

| AGE |  X |
|-----|----|
|  34 | 15 |