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