查询一个表中如age第N大的段数据

查询一个表中如age第N大的段数据

测试表：

MariaDB [jinyong]> show tables;
+-------------------+
| Tables_in_jinyong |
+-------------------+
| user              |
+-------------------+
1 row in set (0.01 sec)
​
MariaDB [jinyong]> desc user;
+-------+--------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra          |
+-------+--------------------+------+-----+---------+----------------+
| id    | int(11)            | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)        | NO   |     | NULL    |                |
| age   | tinyint(4)         | NO   |     | NULL    |                |
| sex   | enum('girl','boy') | YES  |     | NULL    |                |
| skill | varchar(20)        | NO   |     | NULL    |                |
| time  | time               | YES  |     | NULL    |                |
+-------+--------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
//表中有六个字段，且有1061行数据，这里无法全部显示。其中age表示年龄为tinyint类型

下面介绍本人可以想到的一些拙劣的办法去查找第N大"年龄(age)"的数据段，下面方法都是在求第"二"大年龄的行数据。

方法1

方法思路：对数据以年龄进行倒排序，然后使用limit求第一个偏移量后一行数据。

MariaDB [jinyong]> select * from user order by age desc limit 1,1;
+------+------+-----+------+---------------+----------+
| id   | name | age | sex  | skill         | time     |
+------+------+-----+------+---------------+----------+
| 2794 | qTz  | 100 | boy  | J九阴真经     | 18:01:03 |
+------+------+-----+------+---------------+----------+
1 row in set (0.00 sec)

这种方法有bug，如果age最大等于100时，有好几行，则查找出来的不是age第二大的数据。而且如果最大的age有好几行数据怎么办？偏移一次是不够的。如果年龄第二大的数据也有好几行怎么办？只求偏移后一行数据显然也是不对的。

方法2

思路：求出最大age的行数据，使用where过滤掉这些数据(age < max(age))，然后再求出这样的最大的age(就是原本数据的第二大age)，最后使用where age =max2(age)求出这样的数据。

MariaDB [jinyong]> select * from user where age = (select MAX(age) from user where age < (select max(age) from user ORDER BY age desc));
+------+------+-----+------+------------------+----------+
| id   | name | age | sex  | skill            | time     |
+------+------+-----+------+------------------+----------+
| 2412 | Cyo  |  99 | girl | D打狗棍法        | 18:00:23 |
| 2705 | YGa  |  99 | boy  | J九阴真经        | 18:01:01 |
| 2754 | VMh  |  99 | girl | D打狗棍法        | 18:01:02 |
| 2774 | WGR  |  99 | girl | X降龙十八掌      | 18:01:02 |
+------+------+-----+------+------------------+----------+
4 rows in set (0.00 sec)

这种方法解决了多行age相同的问题。但是同样也很痛苦，这里让找年龄第二高的数据，如果是第55高的呢？难道一直迭代？

方法3

思路：使用distinct关键字将age行重复的过滤掉，这样倒排序后的age就不会有重复，然后使用limit 1,1求出第二大年龄的age，再使用一次where过滤出age=第二大的age，即求出需要的第二大age的所有数据。

​
MariaDB [jinyong]> select * from user where age =(select distinct age from user order by age desc limit 1,1); 
+------+------+-----+------+------------------+----------+
| id   | name | age | sex  | skill            | time     |
+------+------+-----+------+------------------+----------+
| 2412 | Cyo  |  99 | girl | D打狗棍法        | 18:00:23 |
| 2705 | YGa  |  99 | boy  | J九阴真经        | 18:01:01 |
| 2754 | VMh  |  99 | girl | D打狗棍法        | 18:01:02 |
| 2774 | WGR  |  99 | girl | X降龙十八掌      | 18:01:02 |
+------+------+-----+------+------------------+----------+
4 rows in set (0.00 sec)

这种方法不但解决了方法1中如果有重复age的问题(distinct关键字)，而且还避免了不停迭代的痛苦(distinct除去冗余后limit的偏移量和偏移后的行数都是绝对指向所求的age)。