大数据第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,账号,金额,充值时间

--建表
create table test_nine_credit_log(
    dist_id string COMMENT '区组id',
    account string COMMENT '账号',
    `money` decimal(10,2) COMMENT '充值金额',
    create_time string COMMENT '订单时间'
)
row format delimited fields terminated by '	';

--插入数据
insert into table test_nine_credit_log values ('1','11',100006,'2019-01-02 13:00:01');
insert into table test_nine_credit_log values ('1','12',110000,'2019-01-02 13:00:02');
insert into table test_nine_credit_log values ('1','13',102000,'2019-01-02 13:00:03');
insert into table test_nine_credit_log values ('1','14',100300,'2019-01-02 13:00:04');
insert into table test_nine_credit_log values ('1','15',100040,'2019-01-02 13:00:05');
insert into table test_nine_credit_log values ('1','18',110000,'2019-01-02 13:00:02');
insert into table test_nine_credit_log values ('1','16',100005,'2019-01-03 13:00:06');
insert into table test_nine_credit_log values ('1','17',180000,'2019-01-03 13:00:07');


insert into table test_nine_credit_log values ('2','21',100800,'2019-01-02 13:00:11');
insert into table test_nine_credit_log values ('2','22',100030,'2019-01-02 13:00:12');
insert into table test_nine_credit_log values ('2','23',100000,'2019-01-02 13:00:13');
insert into table test_nine_credit_log values ('2','24',100010,'2019-01-03 13:00:14');
insert into table test_nine_credit_log values ('2','25',100070,'2019-01-03 13:00:15');
insert into table test_nine_credit_log values ('2','26',100800,'2019-01-02 15:00:11');

insert into table test_nine_credit_log values ('3','31',106000,'2019-01-02 13:00:08');
insert into table test_nine_credit_log values ('3','32',100400,'2019-01-02 13:00:09');
insert into table test_nine_credit_log values ('3','33',100030,'2019-01-02 13:00:10');
insert into table test_nine_credit_log values ('3','34',100003,'2019-01-02 13:00:20');
insert into table test_nine_credit_log values ('3','35',100020,'2019-01-02 13:00:30');
insert into table test_nine_credit_log values ('3','36',100500,'2019-01-02 13:00:40');
insert into table test_nine_credit_log values ('3','37',106000,'2019-01-03 13:00:50');
insert into table test_nine_credit_log values ('3','38',100800,'2019-01-03 13:00:59');

--查询充值日志表2019年1月2号每个区组下充值额最大的账号,要求结果：区组id,账号,金额,充值时间
select
    aaa.dist_id,
    aaa.account,
    aaa.`money`,
    aaa.create_time,
    aaa.money_rank
from
(
    select
        dist_id,
        account,
        `money`,
        create_time,
        dense_rank() over(partition by dist_id order by `money` desc) money_rank   -- dense_rank最完美,因为不仅可以求第一多,而且还可以求第二多,第三多...
    from test_nine_credit_log
    where date_format(create_time,'yyyy-MM-dd') = '2019-01-02'
) aaa
where money_rank = 1;

-- 第二种写法,不用开窗函数
with 
tmp_max_money as(
    select 
        dist_id,
        max(`money`) max
    from test_nine_credit_log
    where date_format(create_time,'yyyy-MM-dd')='2019-01-02'
    group by dist_id
)
select 
    cl.dist_id dist_id,cl.account acount,cl.money money,cl.create_time create_time
from test_nine_credit_log cl 
left join tmp_max_money mm 
on cl.dist_id=mm.dist_id
where cl.money=mm.max and date_format(create_time,'yyyy-MM-dd')='2019-01-02';