下面是另一份面试中的题目:
答案不一定是对的。感觉有点少,我再加点内容吧
嗯直接上代码!可以自行复制到Mysql里面运行。
drop database if exists demo5;
create database demo5 default charset utf8mb4;
use demo5;
-- 第一题
-- 创建登录信息表并插入数据
-- 请你写出一个sql语句查询每个日期新用户的次日留存率,
-- 结果保留小数点后面3位数(3位之后的四舍五入),
-- 并且查询结果按照日期升序排序,上面的例子查询结果如下:
-- 题目:请你统计一下牛客每个日期新用户的次日留存率。
create table tb_login
(
user_id varchar(300) not null comment '用户ID',
login_date date not null comment '登录日期'
) comment '登录信息表';
insert into tb_login
values
('2', '2020/10/12'),
('3', '2020/10/12'),
('1', '2020/10/12'),
('2', '2020/10/13'),
('1', '2020/10/13'),
('3', '2020/10/14'),
('4', '2020/10/14'),
('4', '2020/10/15');
select user_id
, login_date
, min(login_date) over (partition by user_id) as first_date
from tb_login;
-- 方法一:
select first_date
, count(t2.user_id) / count(t1.user_id) as 次留
from (select user_id
, login_date
, min(login_date) over (partition by user_id) as first_date
from tb_login) as t1
left join tb_login as t2
on t1.user_id = t2.user_id
and datediff(t2.login_date, first_date) = 1
group by first_date;
-- 方法二:
select first_date
, sum(case datediff(login_date, first_date) when 1 then 1 else 0 end) / count(distinct user_id) as 次留
from (select user_id
, login_date
, min(login_date) over (partition by user_id) as first_date
from tb_login) as temp
group by first_date;
再来一个!
-- 如果存在就删除名为shops的数据库
drop database if exists `shops`;
-- 创建名为shops的数据库并指定默认的字符集
create database `shops` default charset utf8mb4;
-- 切换到shops数据库
use `shops`;
-- 创建商品信息表
-- 题目:请统计零食类商品中复购率top3高的商品。
create table `tb_product_info`
(
`id` int not null comment '编号',
`product_id` int not null comment '商品ID',
`shop_id` int not null comment '店铺ID',
`tag` varchar(20) not null comment '商品类别标签',
`in_price` int not null comment '进货价格',
`quantity` int not null comment '进货数量',
primary key (`product_id`)
);
-- 插入数据
insert into `tb_product_info` values
(1, 8001, 901, '零食', 60, 1000),
(2, 8002, 901, '饮料', 140, 500),
(3, 8003, 901, '零食', 160, 500);
-- 创建订单总表
create table `tb_order_overall`
(
`id` int not null comment '编号',
`order_id` int not null comment '订单号',
`uid` int not null comment '用户ID',
`event_time` datetime not null comment '下单时间',
`total_amount` int not null comment '订单总金额',
`total_cnt` int not null comment '订单商品总件数',
`status` int not null comment '订单状态',
primary key (`order_id`)
);
insert into `tb_order_overall` values
(1, 301001, 101, '2024/1/30 10:00', 140, 1, 1),
(2, 301002, 102, '2023/10/1 11:00', 235, 2, 1),
(3, 301011, 102, '2024/1/25 11:00', 250, 2, 1),
(4, 301003, 101, '2024/1/26 10:00', 300, 2, 1),
(5, 301013, 105, '2023/9/1 10:00', 300, 2, 1),
(6, 301005, 104, '2023/1/26 10:00', 170, 1, 1);
-- 创建订单明细表
create table `tb_order_detail`
(
`id` int not null comment '编号',
`order_id` int not null comment '订单号',
`product_id` int not null comment '商品ID',
`price` int not null comment '商品单价',
`cnt` int not null comment '下单数量',
primary key (`id`),
foreign key (`order_id`) references tb_order_overall(`order_id`),
foreign key (`product_id`) references tb_product_info(`product_id`),
constraint fk_product_detail unique (`order_id`, `product_id`)
);
insert into `tb_order_detail` values
(1, 301001, 8002, 150, 1),
(2, 301011, 8003, 200, 1),
(3, 301011, 8001, 80, 1),
(4, 301002, 8001, 85, 1),
(5, 301002, 8003, 180, 1),
(6, 301003, 8002, 140, 1),
(7, 301003, 8003, 180, 1),
(8, 301013, 8002, 140, 2),
(9, 301005, 8003, 180, 1);
-- 商品信息表 tb_product_info , 订单总表 tb_order_overall , 订单明细表 tb_order_detail
-- 复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数
## 一、计算近90天内购买它至少两次的人数,订单总表 + 订单明细表 拿到商品ID 对应的下单时间(连表拿字段)
select product_id, count(distinct uid) as cnt_uid
from (select uid
, too.order_id
, product_id
, event_time
from tb_order_overall as too
left join tb_order_detail as tod
on too.order_id = tod.order_id
where current_date() >= event_time
and date_sub(current_date(), interval 89 day) <= event_time) as t
group by product_id
having count('x') > 1;
## 二、计算购买它的总人数,默认订单状态 = 1
select product_id, count(distinct uid)
from (select uid
, product_id
from tb_order_overall as too
left join tb_order_detail as tod
on too.order_id = tod.order_id) as temp
group by product_id;
## 三、人数相除、连上商品信息表
select t3.product_id, rep_rate
from (select t1.product_id, cnt1_uid / cnt2_uid as rep_rate
from (select product_id, count(distinct uid) as cnt1_uid
from (select uid
, too.order_id
, product_id
, event_time
from tb_order_overall as too
left join tb_order_detail as tod
on too.order_id = tod.order_id
where current_date() >= event_time
and date_sub(current_date(), interval 89 day) <= event_time) as t
group by product_id
having count('x') > 1) as t1
left join (select product_id, count(distinct uid) as cnt2_uid
from (select uid
, product_id
from tb_order_overall as too
left join tb_order_detail as tod
on too.order_id = tod.order_id) as temp
group by product_id) as t2
on t1.product_id = t2.product_id) as t3
left join tb_product_info as t4
on t3.product_id = t4.product_id
where tag = '零食'
order by rep_rate desc, id asc
limit 3;
再来一个!
-- 如果存在名为shopp的数据库就删除它
drop database if exists `shopp`;
-- 创建名为shopp的数据库并指定默认的字符集
create database `shopp` default charset utf8mb4;
-- 切换到shopp数据库
use `shopp`;
-- 创建商品信息表
-- 题目:请计算2021年10月商城里所有新用户的首单平均交易金额(客单价)和平均获客成本(保留一位小数)
create table `tb_product_info`
(
`id` int not null comment '编号',
`product_id` int not null comment '商品ID',
`shop_id` int not null comment '店铺ID',
`tag` varchar(10) not null comment '商品类别标签',
`in_price` int not null comment '进货价格',
`quantity` int not null comment '进货数量',
primary key (`product_id`)
);
insert into `tb_product_info` values
(1, 8001, 901, '日用', 60, 1000),
(2, 8002, 901, '零食', 140, 500),
(3, 8003, 901, '零食', 160, 500),
(4, 8004, 902, '零食', 130, 500);
-- 创建订单总表
create table `tb_order_overall`
(
`id` int not null comment '编号',
`order_id` int not null comment '订单号',
`uid` int not null comment '用户ID',
`event_time` datetime not null comment '下单时间',
`total_amount` int not null comment '订单总金额',
`total_cnt` int not null comment '订单商品总件数',
`status` int not null comment '订单状态',
primary key (`order_id`)
);
insert into `tb_order_overall` values
(1, 301001, 101, '2024/1/30 10:00', 140, 1, 1),
(2, 301002, 102, '2023/10/1 11:00', 235, 2, 1),
(3, 301011, 102, '2024/1/25 11:00', 250, 2, 1),
(4, 301003, 101, '2024/1/26 10:00', 300, 2, 1),
(5, 301013, 105, '2023/9/1 10:00', 300, 2, 1),
(6, 301005, 104, '2023/1/26 10:00', 170, 1, 1);
-- 创建订单明细表
create table `tb_order_detail`
(
`id` int not null comment '编号',
`order_id` int not null comment '订单号',
`product_id` int not null comment '商品ID',
`price` int not null comment '商品单价',
`cnt` int not null comment '下单数量',
primary key (`id`),
foreign key (`order_id`) references tb_order_overall(`order_id`),
foreign key (`product_id`) references tb_product_info(`product_id`),
constraint fk_product_detail unique (`order_id`, `product_id`)
);
insert into `tb_order_detail` values
(1, 301002, 8001, 85, 1),
(2, 301002, 8003, 180, 1),
(3, 301003, 8004, 140, 1),
(4, 301003, 8003, 180, 1),
(5, 301005, 8003, 180, 1);
select * from tb_order_detail;
-- 留着当作业了
再来一个!
-- 如果存在就删除名为hrs的数据库
drop database if exists `hrs`;
-- 创建名为hrs的数据库并指定默认的字符集
create database `hrs` default charset utf8mb4;
-- 切换到hrs数据库
use `hrs`;
-- 创建部门表
create table `tb_dept`
(
`dno` int not null comment '编号',
`dname` varchar(10) not null comment '名称',
`dloc` varchar(20) not null comment '所在地',
primary key (`dno`)
);
-- 插入4个部门
insert into `tb_dept` values
(10, '会计部', '北京'),
(20, '研发部', '成都'),
(30, '销售部', '重庆'),
(40, '运维部', '深圳');
-- 创建员工表
create table `tb_emp`
(
`eno` int not null comment '员工编号',
`ename` varchar(20) not null comment '员工姓名',
`job` varchar(20) not null comment '员工职位',
`mgr` int comment '主管编号',
`sal` int not null comment '员工月薪',
`comm` int comment '每月补贴',
`dno` int not null comment '所在部门编号',
primary key (`eno`),
constraint `fk_emp_mgr` foreign key (`mgr`) references tb_emp (`eno`),
constraint `fk_emp_dno` foreign key (`dno`) references tb_dept (`dno`)
);
-- 插入14个员工
insert into `tb_emp` values
(7800, '张三丰', '总裁', null, 9000, 1200, 20),
(2056, '乔峰', '分析师', 7800, 5000, 1500, 20),
(3088, '李莫愁', '设计师', 2056, 3500, 800, 20),
(3211, '张无忌', '程序员', 2056, 3200, null, 20),
(3233, '丘处机', '程序员', 2056, 3400, null, 20),
(3251, '张翠山', '程序员', 2056, 4000, null, 20),
(5566, '宋远桥', '会计师', 7800, 4000, 1000, 10),
(5234, '郭靖', '出纳', 5566, 2000, null, 10),
(3344, '黄蓉', '销售主管', 7800, 3000, 800, 30),
(1359, '胡一刀', '销售员', 3344, 1800, 200, 30),
(4466, '苗人凤', '销售员', 3344, 2500, null, 30),
(3244, '欧阳锋', '程序员', 3088, 3200, null, 20),
(3577, '杨过', '会计', 5566, 2200, null, 10),
(3588, '朱九真', '会计', 5566, 2500, null, 10);
-- 查询员工及其主管的姓名
-- 方法一:左外连接
select t1.ename as emp_name
, t2.ename as mgr_name
from tb_emp as t1
left join tb_emp as t2
on t1.mgr = t2.eno;
-- 方法二:关联子查询
select ename as emp_name
, (select ename
from tb_emp as t2
where t2.eno = t1.mgr) as mgr_name
from tb_emp as t1;
-- 查询月薪最高的员工姓名和月薪
-- 方法一:排序 + limit - 不推荐
select ename
, sal
from tb_emp
order by sal desc
limit 1;
-- 方法二:嵌套查询
select ename
, sal
from tb_emp
where sal = (select max(sal)
from tb_emp);
-- 方法三:all运算
select ename
, sal
from tb_emp
where sal >= all(select sal
from tb_emp);
-- 方法四:计数法
select ename
, sal
from tb_emp as t1
where (select count(*)
from tb_emp as t2
where t2.sal > t1.sal) = 0;
-- 方法五:存在性判断
select ename
, sal
from tb_emp as t1
where not exists (select 'x'
from tb_emp as t2
where t2.sal > t1.sal);
-- 查询月薪前3名的员工的姓名和月薪
select ename
, sal
from tb_emp as t1
where (select count(*)
from tb_emp as t2
where t2.sal > t1.sal) < 3
order by sal desc;
-- 查询员工的姓名和年薪(年薪=(sal+comm)*13)
select ename
, (sal + coalesce(comm, 0)) * 13 as ann_sal
from tb_emp;
-- 查询部门的编号和人数
select dno
, count(*)
from tb_emp
group by dno
with rollup;
-- 查询部门人数超过5个人的部门的编号和人数
select dno
, count(*)
from tb_emp
group by dno
having count(*) > 5;
-- 查询所有部门的名称和人数
select dname
, coalesce(emp_count, 0) as emp_count
from tb_dept
left join (select dno
, count(*) as emp_count
from tb_emp
group by dno) as temp
on tb_dept.dno = temp.dno;
-- 查询月薪超过平均月薪的员工的姓名和月薪
select ename
, sal
from tb_emp
where sal > (select avg(sal)
from tb_emp);
-- 查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪
-- Error Code: 1052. Column 'dno' in field list is ambiguous
-- Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on sal > avg_sal' at line 10
select ename
, dno
, sal
from tb_emp
natural join (select dno
, avg(sal) as avg_sal
from tb_emp
group by dno) as temp
where sal > avg_sal;
-- 查询部门中月薪最高的人姓名、月薪和所在部门名称
select ename
, sal
, dname
from tb_emp
natural join tb_dept
natural join (select dno
, max(sal) as max_sal
from tb_emp
group by dno) as temp
where sal = max_sal;
select ename
, sal
, dname
from tb_emp
natural join tb_dept
where (dno, sal) in (select dno
, max(sal) as max_sal
from tb_emp
group by dno);
-- 查询主管的姓名和职位
select ename
, job
from tb_emp
where eno in (select distinct mgr
from tb_emp
where mgr is not null);
-- 存在:我的员工编号(eno)是别人的主管编号(mgr)
select ename
, job
from tb_emp as t1
where exists (select 'x'
from tb_emp as t2
where t1.eno = t2.mgr);
-- 查询普通员工的姓名和职位
select ename
, job
from tb_emp
where eno not in (select distinct mgr
from tb_emp
where mgr is not null);
select ename
, job
from tb_emp as t1
where not exists (select 'x'
from tb_emp as t2
where t1.eno = t2.mgr);
-- 查询主管和普通员工的平均月薪
select tag
, avg(sal)
from (select sal
, case when exists (select 'x'
from tb_emp as t2
where t1.eno = t2.mgr)
then '主管'
else '普通员工'
end as tag
from tb_emp as t1) as temp
group by tag;
-- 查询月薪排名4~6名的员工排名、姓名和月薪
-- MySQL8 - 窗口函数 - 一套给原始数据添加额外标签的语法 - 专门给数据分析师设计的
select rk1
, ename
, sal
from (select rank() over (order by sal desc) as rk1
, dense_rank() over (order by sal desc) as rk2
, row_number() over (order by sal desc) as rk3
, ename
, sal
from tb_emp) as temp
where rk1 between 4 and 6;
select sal
, dno
, rank() over (partition by dno order by sal desc) as rk5
from tb_emp;
select rank() over (order by sal desc) as rk1
, dense_rank() over (order by sal desc) as rk2
, row_number() over (order by sal desc) as rk3
, lag(sal, 2) over (order by sal desc) as rk4
from tb_emp;
-- MySQL8以下版本的做法
-- 会话变量
select *
from (select @a := @a + 1 as rk
, ename
, sal
from tb_emp, (select @a := 0) as t
order by sal desc) as temp
where rk between 4 and 6;
select *
from (select (select count(*)
from tb_emp as t2
where t2.sal > t1.sal) + 1 as rk
, ename
, sal
from tb_emp as t1) as temp
where rk between 4 and 6
order by rk asc;
-- 查询每个部门月薪排前2名的员工姓名、月薪和部门编号
select ename
, sal
, dno
from (select ename
, sal
, dno
, rank() over (partition by dno order by sal desc) as rk
from tb_emp) as temp
where rk <= 2;
-- MySQL8以下版本没有窗口函数的做法
select ename
, sal
, dno
from tb_emp as t1
where (select count(*)
from tb_emp as t2
where t1.dno = t2.dno
and t2.sal > t1.sal) < 2
order by dno asc, sal desc;
-- 窗口函数补充练习
create table tb_sale
(
sale_month integer not null,
sale_amount integer not null
);
insert into tb_sale
values
(1, 182),
(2, 169),
(3, 325),
(4, 287),
(5, 266),
(6, 355),
(7, 349),
(8, 320),
(9, 297),
(10, 288),
(11, 423),
(12, 311);
-- 统计从1月到12月的累计销售额
-- preceding / following / unbounded / current
select sale_month
, sale_amount
, sum(sale_amount) over (order by sale_month asc
rows between unbounded preceding and current row) as cum_amount
from tb_sale;
-- 上题如果不允许使用窗口函数该怎么做
select sale_month
, (select sum(sale_amount)
from tb_sale as t2
where t2.sale_month <= t1.sale_month) as cum_amount
from tb_sale as t1;
-- 计算逐月环比(如:(二月 - 一月) / 一月)
-- lag() / lead()
select sale_month
, curr_amount
, prev_amount
, round((curr_amount - prev_amount) / prev_amount, 2) as diff_rate
from (select sale_month
, sale_amount as curr_amount
, lag(sale_amount, 1) over (order by sale_month asc) as prev_amount
from tb_sale) as temp;
-- 上题如果不允许使用窗口函数该怎么做 - 自连接
select t1.sale_month
, t1.sale_amount as curr_amount
, t2.sale_amount as prev_amount
from tb_sale t1
left join tb_sale t2
on t1.sale_month = t2.sale_month + 1;
再来一个!
drop database if exists demo4;
create database demo4 default charset utf8mb4;
use demo4;
-- 第一题
-- 创建登录信息表并插入数据
create table tb_user_login
(
user_id varchar(300) not null comment '用户ID',
login_date date not null comment '登录日期'
) comment '登录信息表';
insert into tb_user_login
values
('A', '2019/9/2'),
('A', '2019/9/3'),
('A', '2019/9/4'),
('B', '2019/9/2'),
('B', '2019/9/4'),
('B', '2019/9/10'),
('C', '2019/1/1'),
('C', '2019/1/2'),
('C', '2019/1/30'),
('C', '2019/9/3'),
('C', '2019/9/4'),
('C', '2019/9/5'),
('C', '2019/9/11'),
('C', '2019/9/12'),
('C', '2019/9/13');
-- +---------+------------+
-- | user_id | login_date |
-- +---------+------------+
-- | A | 2019-09-02 |
-- | A | 2019-09-03 |
-- | A | 2019-09-04 |
-- | B | 2019-09-02 |
-- | B | 2019-09-04 |
-- | B | 2019-09-10 |
-- | C | 2019-01-01 |
-- | C | 2019-01-02 |
-- | C | 2019-01-30 |
-- | C | 2019-09-03 |
-- | C | 2019-09-04 |
-- | C | 2019-09-05 |
-- | C | 2019-09-11 |
-- | C | 2019-09-12 |
-- | C | 2019-09-13 |
-- +---------+------------+
-- 根据上面提供的用户登录日志表(假设每个用户每天只有一条登录日志)完成下面的查询。
-- 题目一:查询有连续3天登录行为的用户ID
select distinct user_id
from (select user_id
, date_sub(login_date, interval rk day) as day_rk
from (select user_id
, login_date
, row_number() over () as rk
from tb_user_login) as temp) as t2
group by user_id, day_rk
having count(*) >= 3;
-- 题目二:查询每天新增用户数以及他们次日留存率和三十日留存率
-- 题目三:找出今年连续3天及以上登录网站的用户
-- 题目四:次日留存率、三日留存率、七日留存率、日活/月活、连续登录、同比/环比
select count(t2.user_id)/count(t1.user_id)
from (
select user_id
, min(login_date) as 'first_login'
from tb_user_login
group by user_id) as t1
left join
(
select user_id, login_date
from tb_user_login
group by user_id,login_date) as t2
on t1.user_id = t2.user_id and date_add(t1.first_login,interval 1 day) = t2.login_date;
-- 答案
-- 题目一:
select distinct user_id
from (select user_id
, login_date
, dense_rank() over (partition by user_id order by login_date asc) as seq
from tb_user_login) as temp
group by user_id, date_sub(login_date, interval seq day)
having count(*) >= 3;
-- 题目二:
-- 方法一:
select first_date
, count(t2.user_id) / count(t1.user_id) as 次留
, count(t3.user_id) / count(t1.user_id) as 月留
from (select user_id
, login_date
, min(login_date) over (partition by user_id) as first_date
from tb_user_login) as t1
left join tb_user_login as t2
on t1.user_id = t2.user_id
and datediff(t2.login_date, first_date) = 1
left join tb_user_login as t3
on t1.user_id = t3.user_id
and datediff(t3.login_date, first_date) = 29
group by first_date;
-- 方法二:
select first_date
, sum(case datediff(login_date, first_date) when 1 then 1 else 0 end) / count(distinct user_id) as 次留
, sum(case datediff(login_date, first_date) when 29 then 1 else 0 end) / count(distinct user_id) as 月留
from (select user_id
, login_date
, min(login_date) over (partition by user_id) as first_date
from tb_user_login) as temp
group by first_date;
-- 题目三:
select distinct user_id
from (select user_id
, date_sub(login_date, interval rk day) as day_rk
from (select user_id
, login_date
, row_number() over () as rk
from tb_user_login
where year(login_date) = year(current_date())
) as temp) as t2
group by user_id, day_rk
having count(*) >= 3;
-- 第二题
-- 创建直播间访问日志表并插入数据
create table tb_broadcast_log
(
user_id bigint not null comment '用户ID',
entr_time datetime not null comment '进入时间',
exit_time datetime not null comment '离开时间'
) comment '直播间访问日志表';
insert into tb_broadcast_log values
(1, '2022-10-01 13:30:23', '2022-10-01 17:23:35'),
(2, '2022-10-01 13:35:55', '2022-10-01 16:30:12'),
(3, '2022-10-01 13:42:02', '2022-10-01 18:15:09'),
(4, '2022-10-01 14:12:55', '2022-10-01 15:15:35'),
(5, '2022-10-01 13:30:23', '2022-10-01 17:23:35'),
(6, '2022-10-01 14:30:23', '2022-10-01 20:21:21'),
(7, '2022-10-01 15:30:23', '2022-10-01 21:23:35'),
(8, '2022-10-01 15:35:12', '2022-10-01 18:33:13'),
(9, '2022-10-01 16:01:02', '2022-10-01 17:59:25'),
(10, '2022-10-01 16:15:20', '2022-10-01 18:20:56'),
(11, '2022-10-01 17:31:23', '2022-10-01 19:56:57'),
(12, '2022-10-01 17:32:23', '2022-10-01 18:30:20'),
(10, '2022-10-01 18:25:10', '2022-10-01 20:22:22'),
(1, '2022-10-01 18:30:23', '2022-10-01 21:24:00'),
(2, '2022-10-01 19:18:55', '2022-10-01 19:25:40'),
(3, '2022-10-01 19:20:20', '2022-10-01 20:20:07');
-- 查询直播流量(在线人数)的峰值
-- 第三题
-- 创建比赛记录表并插入数据
create table tb_match
(
player_id int not null comment '选手编号',
match_date date not null comment '比赛日期',
result varchar(10) not null comment '比赛结果'
) comment '比赛记录表';
insert into tb_match values
(1, '2022-10-01', 'win'),
(1, '2022-10-04', 'win'),
(1, '2022-10-08', 'win'),
(1, '2022-10-12', 'draw'),
(1, '2022-11-15', 'win'),
(2, '2022-10-02', 'lose'),
(2, '2022-10-07', 'lose'),
(3, '2022-10-01', 'win'),
(3, '2022-10-03', 'lose'),
(3, '2022-10-08', 'win'),
(3, '2022-10-11', 'win');
-- +-----------+------------+--------+
-- | player_id | match_date | result |
-- +-----------+------------+--------+
-- | 1 | 2022-10-01 | win |
-- | 1 | 2022-10-04 | win |
-- | 1 | 2022-10-08 | win |
-- | 1 | 2022-10-12 | draw |
-- | 1 | 2022-11-15 | win |
-- | 2 | 2022-10-02 | lose |
-- | 2 | 2022-10-07 | lose |
-- | 3 | 2022-10-01 | win |
-- | 3 | 2022-10-03 | lose |
-- | 3 | 2022-10-08 | win |
-- | 3 | 2022-10-11 | win |
-- +-----------+------------+--------+
-- 查询如下所示的每个运动员最大连胜场次。
-- +-----------+-------------+
-- | player_id | max_win_cnt |
-- +-----------+-------------+
-- | 1 | 3 |
-- | 2 | 0 |
-- | 3 | 2 |
-- +-----------+-------------+
再来一个!