大数据第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 \t;

 

 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;

 

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

上一篇:jdbc的快速入门(需要mysql-connector-java-5.1.39-bin.jar包)


下一篇:数据库连接模板(增删改)