1 --建表
2 create table test_nine_credit_log(
3     dist_id string COMMENT ‘区组id‘,
4     account string COMMENT ‘账号‘,
5     `money` decimal(10,2) COMMENT ‘充值金额‘,
6     create_time string COMMENT ‘订单时间‘
7 )
8 row format delimited fields terminated by ‘\t‘;

 1 --插入数据
 2 insert into table test_nine_credit_log values (‘1‘,‘11‘,100006,‘2019-01-02 13:00:01‘);
 3 insert into table test_nine_credit_log values (‘1‘,‘12‘,110000,‘2019-01-02 13:00:02‘);
 4 insert into table test_nine_credit_log values (‘1‘,‘13‘,102000,‘2019-01-02 13:00:03‘);
 5 insert into table test_nine_credit_log values (‘1‘,‘14‘,100300,‘2019-01-02 13:00:04‘);
 6 insert into table test_nine_credit_log values (‘1‘,‘15‘,100040,‘2019-01-02 13:00:05‘);
 7 insert into table test_nine_credit_log values (‘1‘,‘18‘,110000,‘2019-01-02 13:00:02‘);
 8 insert into table test_nine_credit_log values (‘1‘,‘16‘,100005,‘2019-01-03 13:00:06‘);
 9 insert into table test_nine_credit_log values (‘1‘,‘17‘,180000,‘2019-01-03 13:00:07‘);
10 
11 
12 insert into table test_nine_credit_log values (‘2‘,‘21‘,100800,‘2019-01-02 13:00:11‘);
13 insert into table test_nine_credit_log values (‘2‘,‘22‘,100030,‘2019-01-02 13:00:12‘);
14 insert into table test_nine_credit_log values (‘2‘,‘23‘,100000,‘2019-01-02 13:00:13‘);
15 insert into table test_nine_credit_log values (‘2‘,‘24‘,100010,‘2019-01-03 13:00:14‘);
16 insert into table test_nine_credit_log values (‘2‘,‘25‘,100070,‘2019-01-03 13:00:15‘);
17 insert into table test_nine_credit_log values (‘2‘,‘26‘,100800,‘2019-01-02 15:00:11‘);
18 
19 insert into table test_nine_credit_log values (‘3‘,‘31‘,106000,‘2019-01-02 13:00:08‘);
20 insert into table test_nine_credit_log values (‘3‘,‘32‘,100400,‘2019-01-02 13:00:09‘);
21 insert into table test_nine_credit_log values (‘3‘,‘33‘,100030,‘2019-01-02 13:00:10‘);
22 insert into table test_nine_credit_log values (‘3‘,‘34‘,100003,‘2019-01-02 13:00:20‘);
23 insert into table test_nine_credit_log values (‘3‘,‘35‘,100020,‘2019-01-02 13:00:30‘);
24 insert into table test_nine_credit_log values (‘3‘,‘36‘,100500,‘2019-01-02 13:00:40‘);
25 insert into table test_nine_credit_log values (‘3‘,‘37‘,106000,‘2019-01-03 13:00:50‘);
26 insert into table test_nine_credit_log values (‘3‘,‘38‘,100800,‘2019-01-03 13:00:59‘);

 1 --查询充值日志表2019年1月2号每个区组下充值额最大的账号,要求结果：区组id,账号,金额,充值时间
 2 select
 3     aaa.dist_id,
 4     aaa.account,
 5     aaa.`money`,
 6     aaa.create_time,
 7     aaa.money_rank
 8 from
 9 (
10     select
11         dist_id,
12         account,
13         `money`,
14         create_time,
15         dense_rank() over(partition by dist_id order by `money` desc) money_rank   -- dense_rank最完美,因为不仅可以求第一多,而且还可以求第二多,第三多...
16     from test_nine_credit_log
17     where date_format(create_time,‘yyyy-MM-dd‘) = ‘2019-01-02‘
18 ) aaa
19 where money_rank = 1;
20 
21 -- 第二种写法,不用开窗函数
22 with 
23 tmp_max_money as(
24     select 
25         dist_id,
26         max(`money`) max
27     from test_nine_credit_log
28     where date_format(create_time,‘yyyy-MM-dd‘)=‘2019-01-02‘
29     group by dist_id
30 )
31 select 
32     cl.dist_id dist_id,cl.account acount,cl.money money,cl.create_time create_time
33 from test_nine_credit_log cl 
34 left join tmp_max_money mm 
35 on cl.dist_id=mm.dist_id
36 where cl.money=mm.max and date_format(create_time,‘yyyy-MM-dd‘)=‘2019-01-02‘;