使用SQL将人员按性别分类并查询每种性别年纪最大的两个人

在做统计时经常遇到这样的需求，将数据按某种属性分类，按另一属性排序，查询排在前几位的数据信息。如：有一张人员信息表person
id sex name age
1 nan xiaogao 17
2 nv xiaoxu 16
3 nv xiaogang 27
4 nan xiaomao 21
5 ry xiaogou 22
6 nan xiaozhang 15
7 nv xiaoli 17
8 nan xiaolv 18
9 nan xiaocao 19
10 nv xiaoliu 32
11 ry xiaowang 23
要求用SQL查询出每种性别中年龄最大的两位
实现：
SELECT a.* FROM person a WHERE(SELECT COUNT(*) FROM person WHERE sex= a.sex AND age > a.age) <2 ORDER BY a.sex,a.age;

分析：
将person表自乘，得到
id sex name age a.id a.sex a.name a.age
1 nan xiaogao 17 1 nan xiaogao 17
2 nv xiaoxu 16 2 nv xiaoxu 16
3 nv xiaogang 27 3 nv xiaogang 27
4 nan xiaomao 21 4 nan xiaomao 21
5 ry xiaogou 22 5 ry xiaogou 22
6 nan xiaozhang 15 6 nan xiaozhang 15
7 nv xiaoli 17 7 nv xiaoli 17
8 nan xiaolv 18 8 nan xiaolv 18
9 nan xiaocao 19 9 nan xiaocao 19
10 nv xiaoliu 32 10 nv xiaoliu 32
11 ry xiaowang 23 11 ry xiaowang 23

关键语句 SELECT COUNT(*) FROM person WHERE sex= a.sex AND age > a.age 可以将相同性别的人按年龄排序，并获得每个人在排第几，
如xiaogao的年龄时17,根据count(*)统计相同性别比他大的有三个(右边比左边小的)，则按年龄排他排第四。同理可获取其他人按年龄的排名。取前两名则让该数值小于2即可，最后按年龄和性别进行排序。
最终结果：
9 nan xiaocao 19
4 nan xiaomao 21
5 ry xiaogou 22
11 ry xiaowang 23
3 nv xiaogang 27
10 nv xiaoliu 32

如果是在hive或者oracle中还有一个方法 使用row_number()，它可以为每一条记录进行编号（增加一列）。用法如下：
select *,row_number() over (partition by sex order by age desc) as od from person;
得到结果为：
4 nan xiaomao 21 1
9 nan xiaocao 19 2
8 nan xiaolv 18 3
1 nan xiaogao 17 4
6 nan xiaozhang 16 5
10 nv xiaoliu 32 1
3 nv xiaogang 27 2
7 nv xiaoli 17 3
2 nv xiaoxu 16 4
11 ry xiaowang 23 1
5 ry xiaogou 22 2
将数据输入到一个新表：
INSERT OVERWRITE TABLE person2
select *,row_number() over (partition by sex order by age desc) as od from person;
最后查询：
select * from person2 where od<3 order by sex,age;