MySQL 高级SQL进阶真题讲解(一)

一、题目描述

二、数据构建

drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL, 
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, 'm');
INSERT INTO author_tb VALUES(102 , 1, 'f');
INSERT INTO author_tb VALUES(103 , 1, 'm');
INSERT INTO author_tb VALUES(104 , 3, 'm');
INSERT INTO author_tb VALUES(105 , 4, 'f');
INSERT INTO author_tb VALUES(106 , 2, 'f');
INSERT INTO author_tb VALUES(107 , 2, 'm');
INSERT INTO author_tb VALUES(108 , 5, 'f');
INSERT INTO author_tb VALUES(109 , 6, 'f');
INSERT INTO author_tb VALUES(110 , 5, 'm');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);

三、题目答案

with cte1  as
(
select author_id,answer_date
from answer_tb
group  by author_id,answer_date
)
,
cte2 as
(
 select *,
 datediff(answer_date,lag(answer_date) over(partition by  author_id order by answer_date))  as flag
 from cte1
)
,cte3 as
(
select * ,
case when flag = 1 then  @inc :=  @inc  + 1 else @inc :=1 end as inc
from cte2,(select @inc := 0) as t
)
select a.author_id,b.author_level,max(a.inc) cnt from cte3 as a
inner join author_tb b on a.author_id = b.author_id
group by  a.author_id,b.author_level
having cnt >= 3

四、题目讲解

第一步

with cte1  as
(
select author_id,answer_date
from answer_tb
group  by author_id,answer_date
)

最终求连续天数，同一天的留一条SQL就可以了。所以这条语句就是按author_id与answer_date分组 group 去重。
第二步

cte2 as
(
 select *,
 datediff(answer_date,lag(answer_date) over(partition by  author_id order by answer_date))  as flag
 from cte1
)

第二步，利用窗口函数求出每个 author_id 的每一行与上一行的时间间隔。= 1 就代表是连续的

第三步

cte3 as
(
select * ,
case when flag = 1 then  @inc :=  @inc  + 1 else @inc :=1 end as inc
from cte2,(select @inc := 0) as t
)

利用虚拟表，与伪列。算出连续天数 inc就为连续的天数
第四步。

select a.author_id,b.author_level,max(a.inc) cnt from cte3 as a
inner join author_tb b on a.author_id = b.author_id
group by  a.author_id,b.author_level
having cnt >= 3

取最大的连续天数，且大于3天的数据展示出来。

对第二步开窗函数不熟悉的可参考我的开窗函数专辑说明
开窗函数