当前位置：首页 > article >正文

MySQL面试题--连续三天登录(困难)

article 2024/9/19 8:29:22

一、准备工作

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'),
    (102, 1, 'f'),
    (103, 1, 'm'),
    (104, 3, 'm'),
    (105, 4, 'f'),
    (106, 2, 'f'),
    (107, 2, 'm'),
    (108, 5, 'f'),
    (109, 6, 'f'),
    (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),
    ('2021-11-2', 101, 'E002', 200),
    ('2021-11-3', 101, 'E002', 201),
    ('2021-11-5', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 101, 'E002', 201),
    ('2021-11-1', 102, 'C003', 50),
    ('2021-11-1', 103, 'P001', 35),
    ('2021-11-1', 104, 'C003', 120),
    ('2021-11-1', 105, 'P001', 125),
    ('2021-11-1', 102, 'P002', 105),
    ('2021-11-2', 101, 'P001', 201),
    ('2021-11-3', 101, 'P002', 211),
    ('2021-11-2', 101, 'P001', 211),
    ('2021-11-2', 115, 'P001', 211),
    ('2021-11-4', 115, 'P001', 211),
    ('2021-11-6', 115, 'P001', 211),
    ('2021-11-8', 115, 'P001', 211),
    ('2021-11-7', 116, 'P001', 211),
    ('2021-11-8', 116, 'P001', 211),
    ('2021-11-9', 116, 'P001', 211),
    ('2021-11-12', 116, 'P001', 211),
    ('2021-11-2', 110, 'C003', 200),
    ('2021-11-2', 110, 'C001', 225),
    ('2021-11-2', 110, 'C002', 220),
    ('2021-11-3', 101, 'C002', 180),
    ('2021-11-4', 109, 'E003', 130),
    ('2021-11-4', 109, 'E001', 123),
    ('2021-11-5', 108, 'C001', 160),
    ('2021-11-5', 108, 'C002', 120),
    ('2021-11-5', 110, 'P001', 180),
    ('2021-11-5', 106, 'P002', 45),
    ('2021-11-5', 107, 'E003', 56),
    ('2021-11-6', 107, 'E003', 56),
    ('2021-11-7', 120, 'E003', 56),
    ('2021-11-8', 120, 'E003', 56),
    ('2021-11-10', 120, 'E003', 56),
    ('2021-11-14', 120, 'E003', 56),
    ('2021-11-12', 120, 'E003', 56);

# 查询连续登录三天的人员信息

输入1：（展示了部分数据）

输入2：

输出：

二、分析

三、实现

with t as (                           -- 首先对id和时间进行去重操作
    select distinct
        answer_date,author_id
    from answer_tb           
), t1 as (                        --  对id进行分组然后对时间升序排序
    select
        answer_date,author_id,
        row_number() over (partition by author_id order by answer_date) rn
    from t
), t2 as(                           --  截取时间的天与排序求差值
    select
        author_id,
        rn,
        day(answer_date)-rn 差值
    from t1
), t3 as (                     --  对id和差值分组 聚合相同差值
    select
        author_id,
        差值,
        count(差值) 连续登录天数
    from t2
    group by author_id,差值
), t4 as (                                   --  加条件 聚合差值总数大于等于3 的
    select * from t3 where 连续登录天数 >=3
)
    select                                 --  左连接 
        t4.author_id,a.author_level,
        连续登录天数 
    from t4 
    left join author_tb a on a.author_id = t4.author_id;