频道栏目
首页 > 数据库 > SQL Server > 正文
使用SQL将人员按性别分类并查询每种性别年纪最大的两个人
2017-07-15 10:40:08      个评论    来源:talent_curry的博客  
收藏   我要投稿

使用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;

点击复制链接 与好友分享!回本站首页
上一篇:SQLserver2008修改密码,添加新用户的操作方法
下一篇:利用SQL索引提高查询速度
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训

版权所有: 红黑联盟--致力于做实用的IT技术学习网站