首页 > 试题广场 >

筛选限定昵称成就值活跃日期的用户

[编程题]筛选限定昵称成就值活跃日期的用户

热度指数：44763 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

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

由示例数据结果输出如下：

解释：昵称以『牛客』开头『号』结尾且成就值在1200~2500之间的有1002、1004；

1002最近一次试卷区开始作答时间2021年9月，交卷时间为2021年9月；1004最近一次试卷区开始作答时间为2021年8月，未交卷。

因此最终满足条件的只有1002。

示例1

输入

drop table if exists user_info,exam_record,practice_record;
CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 1000, 2, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  (1003, '进击的3号', 2200, 5, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 2500, 6, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 3000, 7, 'C++', '2020-01-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8002, '2021-09-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:59', 80),
(1001, 9001, '2021-05-02 10:01:01', null, null),
(1001, 9002, '2021-02-02 19:01:01', '2021-02-02 19:30:01', 87),
(1001, 9001, '2021-06-02 19:01:01', '2021-06-02 19:32:00', 20),
(1001, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1001, 9002, '2021-09-01 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', '2021-05-05 18:59:02', 90),
(1003, 9003, '2021-02-06 12:01:01', null, null),
(1003, 9001, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 89),
(1004, 9002, '2021-08-06 12:01:01', null, null),
(1002, 9001, '2020-01-01 12:01:01', '2020-01-01 12:31:01', 81),
(1002, 9002, '2020-02-01 12:01:01', '2020-02-01 12:31:01', 82),
(1002, 9002, '2020-02-02 12:11:01', '2020-02-02 12:31:01', 83),
(1005, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1006, 9001, '2021-02-01 11:01:01', '2021-02-01 11:31:01', 84),
(1002, 9001, '2021-09-06 12:01:01', '2021-09-06 12:21:01', 80),
(1002, 9001, '2021-09-06 12:01:01', null, null),
(1002, 9001, '2021-09-07 12:01:01', null, null);

输出

1002|牛客2号|1200

算法知识视频讲解

Mysql

logilong

select uid, nick_name, achievement
from user_info
where nick_name like '牛客%号'
    and achievement between 1200 and 2500
    and uid in (
        select uid
        from (
            select uid, start_time as 'act_time'
            from exam_record
            union all
            select uid, submit_time as 'act_time'
            from practice_record
        ) as a
        group by uid
        having date_format(max(act_time), '%Y%m')='202109'
    )

发表于 2021-11-03 11:16:52 回复(7)

Mysql

冷凡社长

select uid,nick_name,achievement
from user_info
where nick_name like "牛客%号" and achievement between 1200 and 2500
and uid in (
        select uid
        from (
            select uid, start_time as 'act_time'        from exam_record
            union all
            select uid, submit_time as 'act_time'       from practice_record
        ) as a
        group by uid
        having date_format(max(act_time), '%Y%m')='202109'
    )

这道题的重点是需要在练习和答题中找到最近一次活跃在九月的。

发表于 2022-08-24 13:26:12 回复(0)

Mysql

啊坡糍嘚

select 
    uf.uid,nick_name,achievement
from user_info uf 
left join exam_record er on uf.uid=er.uid
left join practice_record pr on uf.uid=pr.uid
where nick_name like('牛客%号')
    and achievement between 1200 and 2500
group by uid 
having substring(max(er.start_time),1,7)='2021-09'&nbs***bsp;substring(max(pr.submit_time),1,7)='2021-09'

发表于 2021-11-22 18:39:40 回复(0)

Mysql

mikisama

with active_table as
(select uid, max(active_time) as last_active_time
 from 
    (select uid, date_format(start_time,'%Y%m') as active_time
     from exam_record
     union all
     select uid, date_format(submit_time, '%Y%m') as active_time
     from practice_record
    )t
 group by uid
)

select uid, nick_name, achievement
from user_info
join  active_table
using(uid)
where nick_name like '牛客%' and nick_name like '%号'
and achievement>=1200 and achievement<=2500
and last_active_time = '202109'

发表于 2021-11-17 14:42:49 回复(1)

Mysql

黄巍1号

SELECT distinct ui.uid,nick_name,achievement
from user_info as ui
left join exam_record as er on ui.uid = er.uid
left join practice_record as pr on ui.uid = pr.uid
where nick_name like '牛客%号' and achievement between 1200 and 2500
and
(DATE_FORMAT(er.start_time,"%Y%m") = 202109 or DATE_FORMAT(er.submit_time,"%Y%m") = 202109
or DATE_FORMAT(pr.submit_time,"%Y%m") = 202109);

发表于 2021-11-15 13:15:25 回复(0)

不想长大诶

select uid,nick_name,achievement
from user_info
where nick_name like '牛客%' and nick_name like '%号' and achievement between 1200 and 2500
      and uid in (
           select a.uid
           from (select *,row_number() over(partition by uid order by date_format(submit_time,'%Y%m') desc) as prac_rank 
                 from practice_record) a
           where prac_rank=1 and date_format(submit_time,'%Y%m')='202109'
           union
           select b.uid
           from (select *,ROW_NUMBER() over(partition by uid order by DATE_FORMAT(start_time,'%Y%m') desc) as exam_rank 
                 from exam_record) b
           where exam_rank=1 and DATE_FORMAT(start_time,'%Y%m')='202109'
       )

发表于 2021-10-22 14:19:56 回复(0)

Mysql

PythonNoo6

一定要用LEFT JOIN，如果用JOIN可能因为考试表或练习表没有没有某些用户行而丢失用户数据

SELECT  t.uid,t.nick_name,t.achievement
FROM (select DISTINCT ui.uid,ui.nick_name,ui.achievement,MAX(er.start_time) OVER(PARTITION BY ui.uid) max_exam,
             MAX(pr.submit_time) OVER(PARTITION BY ui.uid) max_question
      FROM user_info ui
      LEFT JOIN exam_record er 
      ON ui.uid = er.uid 
      LEFT JOIN practice_record pr 
      ON pr.uid = ui.uid
      WHERE ui.nick_name LIKE  "牛客%号" AND (ui.achievement BETWEEN 1200 AND 2500)) t
WHERE DATE_FORMAT(t.max_exam,"%Y%m") = 202109&nbs***bsp;DATE_FORMAT(t.max_question,"%Y%m") = 202109

发表于 2022-04-07 10:45:49 回复(2)

Mysql

牛客153584147号

#条件：牛客开头，客结尾，1200-1500成就度，活跃2021年9月
select t1.uid,nick_name,achievement
from user_info t1 left join exam_record t2 on t1.uid=t2.uid
left join practice_record t3 on t2.uid=t3.uid
where nick_name like '牛客%号' and achievement between 1200 and 2500
group by t1.uid

having DATE_FORMAT(max(t3.submit_time),'%Y%m')='202109' or DATE_FORMAT(max(t2.start_time),'%Y%m')='202109';

###'%Y%m'='202109'不是'2021-09'

发表于 2022-08-18 11:26:39 回复(0)

Mysql

一个好懒的人

select
uid,
nick_name,
achievement
from exam_record er
right join user_info ui using(uid)
left join practice_record pr using(uid)
where nick_name like '牛客%号' and achievement between 1200 and 2500
group by uid
having date_format(max(er.start_time),'%Y%m') = '202109' or
date_format(max(pr.submit_time),'%Y%m') = '202109'

发表于 2022-08-14 21:13:33 回复(0)

Mysql

桃桃美奈子

select uid, nick_name, achievement
from user_info
where nick_name like "牛客%号"
and achievement between 1200 and 2500
and uid in(
select uid
from exam_record
group by uid
having max(date_format(start_time,"%Y%m")) = "202109"
union
select uid
from practice_record
group by uid
having max(date_format(submit_time,"%Y%m")) = "202109")

发表于 2022-04-18 16:17:30 回复(0)

Mysql

XXCC111111

select distinct a.uid, a.nick_name, achievement
from user_info a
left join (select uid,
           max(date_format(start_time,'%Y%m')) over(partition by uid) recent_month_r
           from exam_record
) b
on a.uid = b.uid
left join (select uid,
           max(date_format(submit_time,'%Y%m')) over(partition by uid) recent_month_p
           from practice_record
) c
on a.uid = c.uid
where (recent_month_p = 202109 or recent_month_r = 202109)
and a.nick_name like '牛客%号'
and achievement between 1200 and 2500;

发表于 2022-03-23 10:24:03 回复(0)

Mysql

牛客547999472号

本题全连接方法
#本题中：
——user_info中用户名有1001到1006等六个
——exam_record中有用户名1001到1005等五个
——practice_record中有用户名等01、02、04、06等四个

#要完成表之间的全连接（MySQL没有专门全连接语法）有两种方法：
——1、以user为主表进行连接（exam right join user left join practice）；
——2、用union 来完成exam和practic这两个表的全连接（如下）；

select uid
,nick_name,
achievement
from user_info
where nick_name like '牛客%号' and achievement between 1200 and 2500
and 
uid in 
(
    select uid
    from 
    (
    select uid
    ,max(date_format(start_time,'%Y%m')) as aa
    from exam_record
    group by uid
    having aa = 202109 
    #order by uid
    ) a
    
    union 
    
    select uid
    from 
    (
    select uid
    ,max(date_format(submit_time,'%Y%m')) as bb
    from practice_record
    group by uid
    having bb = 202109 
    #order by uid 
    ) b
)

编辑于 2021-12-23 11:15:58 回复(0)

牛客392104363号

select distinct a.uid,nick_name,achievement
from user_info a left join exam_record b on a.uid=b.uid left join practice_record c on a.uid=c.uid
where nick_name like '牛%号' and (achievement between 1200 and 2500)

and (date_format(b.start_time,'%Y%m')=202109 or date_format(c.submit_time,'%Y%m')=202109)

记得要用distinct,每次都忘！！！

日期的判断之间用了or，必须用括号将这两句判断括起来，不然因SQL先判断and，会将满足了or后的条件也筛选出来，结果会多出其他uid。

发表于 2021-11-09 14:10:13 回复(3)

ania-07

select uid,
nick_name,
achievement
from user_info
where nick_name like '牛客%'
and nick_name like '%号'
and achievement between 1200 and 2500
and uid in(
select a.uid
from(
select uid,date_format(start_time,'%Y%m') d1
from exam_record
where date_format(start_time,'%Y%m')='202109'
order by d1 desc
limit 1) a
union
select b.uid
from(
select uid,date_format(submit_time,'%Y%m') d2
from practice_record
where date_format(submit_time,'%Y%m')='202109'
order by d2 desc
limit 1) b
)

发表于 2021-10-28 20:10:10 回复(0)

Mysql

hchwxc

# 计算试卷区最近活跃时间
with t1 as (
    select 
        distinct uid,
        max(date_format(start_time,"%Y%m")) 
        over(partition by uid) as start_month  
    from 
        exam_record 
)

# 计算题目区最近活跃时间
, t2 as (
    select 
        distinct uid,
        max(date_format(submit_time,"%Y%m")) 
        over(partition by uid) as submit_month  
    from 
        practice_record  
)
# 两张表合并 计算出每个用户的试卷区和题目区最近活跃时间
,t3 as(
    select 
        t1.uid,
        start_month,
        submit_month 
    from 
        t1
    left join 
        t2 
    on t1.uid =t2.uid
)
# 通过比较试卷区和题目区最近的活跃时间，求出最近的活跃时间
,t4 as (
    select 
        uid,
        (case 
            when start_month>=submit_month 
            then start_month
            when start_month<submit_month 
            then submit_month 
            when start_month is null 
            and submit_month is not null
            then submit_month
            else 
                start_month
            end 
        ) as recent_month
    from t3
)
# 筛选满足条件的字段
select
    t4.uid,
    nick_name,
    achievement
from t4,user_info
where t4.uid=user_info.uid
and nick_name like '牛客%号'
and achievement between 1200 and 2500
and recent_month=202109

发表于 2023-12-18 15:17:31 回复(0)

Mysql

War张

-- 1:
select
    uid,
    nick_name,
    achievement
from user_info
where nick_name like '牛客%号'
and achievement between 1200 and 2500
and uid in (select uid from exam_record group by uid having date_format(max(start_time),'%Y-%m') = '2021-09'
            union 
            select uid from practice_record group by uid having date_format(max(submit_time),'%Y-%m') = '2021-09')
-- 2:
with t as (
    select uid,start_time active_time from exam_record
    union all
    select uid,submit_time active_time from practice_record
)
select uid,nick_name,achievement from user_info
where nick_name like '牛客%号'
and achievement between 1200 and 2500
and uid in (select uid from t group by uid having date_format(max(active_time),'%Y-%m') = '2021-09')

发表于 2023-08-29 20:56:26 回复(0)

你滴铁牛

select distinct exam_record.uid,nick_name,achievement
from exam_record
left join user_info
on exam_record.uid=user_info.uid
left join practice_record
on practice_record.uid=exam_record.uid
where nick_name like '牛客%号' 
and achievement between 1200 and 2500
and (DATE_FORMAT(exam_record.submit_time,'%Y.%m')='2021.09'
or DATE_FORMAT(practice_record.submit_time,'%Y.%m')='2021.09')

发表于 2023-07-11 16:26:55 回复(0)

爱吃香菜的打工鸭求offer

SELECT uid,nick_name,achievement
FROM user_info ui
WHERE nick_name LIKE "牛客%号" AND achievement BETWEEN 1200 AND 2500
AND uid IN (
SELECT DISTINCT er.uid
FROM exam_record er LEFT JOIN user_info ui 
ON er.uid = ui.uid
LEFT JOIN practice_record pr
ON er.uid = pr.uid
WHERE DATE_FORMAT(pr.submit_time,'%Y%m') = 202109&nbs***bsp;DATE_FORMAT(er.start_time,'%Y%m') = 202109);

发表于 2023-06-28 15:32:02 回复(0)

Vegevege

select a.uid, a.nick_name, a.achievement
from user_info as a
left join exam_record as b on a.uid=b.uid
left join practice_record as c on a.uid= c.uid
group by a.uid
having nick_name like ("牛客_号") and (achievement between 1200 and 2500)
    and (max(date_format(start_time, '%Y%m'))=202109&nbs***bsp;max(date_format(c.submit_time, '%Y%m'))=202109)

发表于 2023-03-15 09:43:51 回复(0)

Mysql

牛客484721424号

select 
e.uid uid,
nick_name,
achievement
from exam_record e
left join user_info u
on e.uid=u.uid
where nick_name like '牛客%号' 
and year(start_time)=2021 and month(start_time)=9
and achievement between 1200 and 2500
union
select p.uid uid,
nick_name,
achievement
from practice_record p
left join user_info u
on p.uid=u.uid
where nick_name like '牛客%号' 
and year(submit_time)=2021 and month(submit_time)=9
and achievement between 1200 and 2500

试卷和练习两张表分别求出结果，然后用union拼接直接去重

发表于 2022-12-30 10:43:36 回复(0)