有一个账号表如下,请写出SQL语句,查询各自区组的money排名前十的账号(分组取前10)
1)建表(MySQL)
CREATE TABLE `test_ten_account`
(
`dist_id` int COMMENT '区组id',
`account` string COMMENT '账号',
`gold` int COMMENT '金币'
)
row format delimited fields terminated by ',';
insert into table test_ten_account values ('1','11',100006);
insert into table test_ten_account values ('1','12',110000);
insert into table test_ten_account values ('1','13',102000);
insert into table test_ten_account values ('1','14',100300);
insert into table test_ten_account values ('1','15',100040);
insert into table test_ten_account values ('1','18',110000);
insert into table test_ten_account values ('1','16',100005);
insert into table test_ten_account values ('1','17',180000);
insert into table test_ten_account values ('2','21',100800);
insert into table test_ten_account values ('2','22',100030);
insert into table test_ten_account values ('2','23',100000);
insert into table test_ten_account values ('2','24',100010);
insert into table test_ten_account values ('2','25',100070);
insert into table test_ten_account values ('2','26',100800);
insert into table test_ten_account values ('3','31',106000);
insert into table test_ten_account values ('3','32',100400);
insert into table test_ten_account values ('3','33',100030);
insert into table test_ten_account values ('3','34',100003);
insert into table test_ten_account values ('3','35',100020);
insert into table test_ten_account values ('3','36',100500);
insert into table test_ten_account values ('3','37',106000);
insert into table test_ten_account values ('3','38',100800);
2)最终SQL
with a as (select dist_id,
account,
gold,
rank() over (distribute by dist_id sort by gold) `rn`
from test_ten_account
)
select dist_id, account, gold, rn
from a
where a.rn <= 10
;
总结
就是简单的分组求Top N类型