大数据第55天—Mysql练习题12道之十-查询各自区组的money排名前十的账号-杨大伟

有一个账号表如下,请写出SQL语句,查询各自区组的money排名前十的账号（分组取前10）

 1 -- CREATE TABIE `account` 
 2 -- (
 3 --     `dist_id` int（11）
 4 --     DEFAULT NULL COMMENT '区组id',
 5 --     `account` varchar（100）DEFAULT NULL COMMENT '账号' ,
 6 --     `gold` int（11）DEFAULT NULL COMMENT '金币' 
 7 --     PRIMARY KEY （`dist_id`,`account_id`）,
 8 -- ）ENGINE=InnoDB DEFAULT CHARSET-utf8
 9 -- 替换成hive表
10 drop table if exists `test_ten_account`;
11 create table `test_ten_account`(
12     `dist_id` string COMMENT '区组id',
13     `account` string COMMENT '账号',
14     `gold` bigint COMMENT '金币'
15 )
16 row format delimited fields terminated by '	';

 1 insert into table test_ten_account values ('1','11',100006);
 2 insert into table test_ten_account values ('1','12',110000);
 3 insert into table test_ten_account values ('1','13',102000);
 4 insert into table test_ten_account values ('1','14',100300);
 5 insert into table test_ten_account values ('1','15',100040);
 6 insert into table test_ten_account values ('1','18',110000);
 7 insert into table test_ten_account values ('1','16',100005);
 8 insert into table test_ten_account values ('1','17',180000);
 9 
10 insert into table test_ten_account values ('2','21',100800);
11 insert into table test_ten_account values ('2','22',100030);
12 insert into table test_ten_account values ('2','23',100000);
13 insert into table test_ten_account values ('2','24',100010);
14 insert into table test_ten_account values ('2','25',100070);
15 insert into table test_ten_account values ('2','26',100800);
16 
17 insert into table test_ten_account values ('3','31',106000);
18 insert into table test_ten_account values ('3','32',100400);
19 insert into table test_ten_account values ('3','33',100030);
20 insert into table test_ten_account values ('3','34',100003);
21 insert into table test_ten_account values ('3','35',100020);
22 insert into table test_ten_account values ('3','36',100500);
23 insert into table test_ten_account values ('3','37',106000);
24 insert into table test_ten_account values ('3','38',100800);

 1 select
 2     dist_id,
 3     account,
 4     gold,
 5     gold_rank
 6 from
 7 (
 8     select
 9         `dist_id`,
10         `account`,
11         `gold`,
12         dense_rank() over(partition by dist_id order by gold desc) gold_rank
13     from test_ten_account
14 ) tmp
15 where gold_rank <= 3;

相关阅读:
Piggy-Bank （hdoj1114）
Word Amalgamation（hdoj1113）
Lowest Bit（hdoj1196）
1206: B.求和
1207: C.LU的困惑
STL初步
关于521(nyoj)
first blood暴力搜索，剪枝是关键
变态最大值（nyoj）
烧饼（nyoj779）

原文地址：https://www.cnblogs.com/shui68home/p/13683994.html