使用SQL语句找到表中某列的第几名

SQL> select * from t1;

        ID        AGE
---------- ----------
         1         20
         2         19
         3         19
         4         21
         5         22
         6         27

6 rows selected.

 

现在要求找出表中第三年轻的学生

 

方法1

第三年轻，也就意味着只有两个人比他小

SQL> select t11.*
  2  from t1 t11
  3  where 2=(select count(*) from t1 t22 where t11.age>t22.age);

        ID        AGE
---------- ----------
         1         20

 

方法2

使用窗口函数

SQL> select id,age
  2  from
  3  (
  4  select id,age,
  5  dense_rank() over(order by age) dr
  6  from t1
  7  )
  8  where dr=3;

        ID        AGE
---------- ----------
         4         21

 

奇怪了，这里结果为什么不一样呢？回头看一下表中的数据，有两条age=19的数据，这就是原因。下面换rank

SQL> select id,age
  2  from
  3  (
  4  select id,age,
  5  rank() over(order by age) dr
  6  from t1
  7  )
  8  where dr=3;

        ID        AGE
---------- ----------
         1         20

 

通过如下两个查询可以看出来DENSE_RANK和RANK的区别

SQL> select id,age,
  2  rank() over(order by age) dr
  3  from t1;

        ID        AGE         DR
---------- ---------- ----------
         3         19          1
         2         19          1
         1         20          3
         4         21          4
         5         22          5
         6         27          6

6 rows selected.

SQL> select id,age,
  2  dense_rank() over(order by age) dr
  3  from t1;

        ID        AGE         DR
---------- ---------- ----------
         3         19          1
         2         19          1
         1         20          2
         4         21          3
         5         22          4
         6         27          5

6 rows selected.

 

方法3

使用自连接

SQL> select t11.*
  2  from t1 t11,t1 t22,t1 t33
  3  where t11.age>t22.age
  4  and t22.age>t33.age
  5  and t11.id not in
  6  (
  7  select t44.id
  8  from t1 t44,t1 t55,t1 t66,t1 t77
  9  where t44.age>t55.age
 10  and t55.age>t66.age
 11  and t66.age>t77.age
 12  );

        ID        AGE
---------- ----------
         4         21
         4         21

 

啊哦，又出错了，这里还是同样的原因，由于表中存在重复数据。这个改起来有些麻烦，我们就先把表中的那条重复数据取掉吧，毕竟这里真正的目的是这个SQL写法的思路

SQL> delete from t1 where id=2;

1 row deleted.

SQL> select * from t1 order by age;

        ID        AGE
---------- ----------
         3         19
         1         20
         4         21
         5         22
         6         27

SQL> select t11.*
  2  from t1 t11,t1 t22,t1 t33
  3  where t11.age>t22.age
  4  and t22.age>t33.age
  5  and t11.id not in
  6  (
  7  select t44.id
  8  from t1 t44,t1 t55,t1 t66,t1 t77
  9  where t44.age>t55.age
 10  and t55.age>t66.age
 11  and t66.age>t77.age
 12  );

        ID        AGE
---------- ----------
         4         21