题解 147 | #筛选限定昵称成就值活跃日期的用户#

【场景】：模糊查询、在...之间

【分类】：比较运算符、like、between

分析思路

难点：

1.最近一次活跃（答题或作答试卷）在2021年9月的用户信息 条件 需要查询出每个用户最近活跃（答题或作答试卷）的日期才能判断这个日期是否在2021年9月

（1）合并用户答题或作答试卷记录

• [使用]：union

（2）统计用户最近一次活跃记录

• [条件]：max(time) as max_time ... group by uid

（3）找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息

最近一次活跃（答题或作答试卷在2021年9月的用户信息

• [条件]：max_time ='202109'

统计昵称以『牛客』开头『号』结尾

• [条件]：nick_name like '牛客%号'

成就值在1200~2500之间

• [条件]：achievement between 1200 and 2500

求解代码

方法一：

with子句

with
    main as(
        #合并用户答题或作答试卷记录
        (select
            uid,
            date_format(start_time,'%Y%m') as time
        from exam_record)
        union
        (select
            uid,
            date_format(submit_time,'%Y%m') as time
        from practice_record)
    )
    ,main1 as(
        #统计用户最近一次活跃记录
        select
            uid,
            max(time)as max_time
        from main
        group by uid
    )
#找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息
select
    a.uid,
    nick_name,
    achievement
from user_info a
join main1 using(uid)
where max_time = '202109' 
and nick_name like '牛客%号'
and (achievement between 1200 and 2500)

方法二：

多表连接

#找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息
select
    a.uid,
    nick_name,
    achievement
from user_info a
join(
    #统计用户最近一次活跃记录
    select
        uid,
        max(time)as max_time
    from(
        #合并用户答题或作答试卷记录
        (select
            uid,
            date_format(start_time,'%Y%m') as time
        from exam_record)
        union
        (select
            uid,
            date_format(submit_time,'%Y%m') as time
        from practice_record)
    ) main
    group by uid
) main1 using(uid)
where max_time = '202109' 
and nick_name like '牛客%号'
and (achievement between 1200 and 2500)

方法三

#找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息
select
    uid,
    nick_name,
    achievement
from user_info a
left join exam_record b using(uid)
left join practice_record c using(uid)
where nick_name like '牛客%号'
and achievement between 1200 and 2500
group by uid
having date_format(max(b.start_time),'%Y%m') = '202109' or
date_format(max(c.submit_time),'%Y%m') = '202109'