大数据第54天—Mysql练习题12道之九-充值日志表-杨大伟
-- 有一个充值日志表如下：
-- CREATE TABLE `credit log`
-- (
-- `dist_id` int（11）DEFAULT NULL COMMENT '区组id',
-- `account` varchar（100）DEFAULT NULL COMMENT '账号',
-- `money` int(11) DEFAULT NULL COMMENT '充值金额',
-- `create_time` datetime DEFAULT NULL COMMENT '订单时间'
-- )ENGINE=InnoDB DEFAUILT CHARSET-utf8
-- 请写出SQL语句,查询充值日志表2015年7月9号每个区组下充值额最大的账号,要求结果：
-- 区组id,账号,金额,充值时间
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';
