近三个月未完成试卷数为0的用户完成情况

现有试卷作答记录表exam_record（uid:用户ID, exam_id:试卷ID, start_time:开始作答时间, submit_time:交卷时间，为空的话则代表未完成, score:得分）：

id	uid	exam_id	start_time	submit_time	score
1	1006	9003	2021-09-06 10:01:01	2021-09-06 10:21:02	84
2	1006	9001	2021-08-02 12:11:01	2021-08-02 12:31:01	89
3	1006	9002	2021-06-06 10:01:01	2021-06-06 10:21:01	81
4	1006	9002	2021-05-06 10:01:01	2021-05-06 10:21:01	81
5	1006	9001	2021-05-01 12:01:01	(NULL)	(NULL)
6	1001	9001	2021-09-05 10:31:01	2021-09-05 10:51:01	81
7	1001	9003	2021-08-01 09:01:01	2021-08-01 09:51:11	78
8	1001	9002	2021-07-01 09:01:01	2021-07-01 09:31:00	81
9	1001	9002	2021-07-01 12:01:01	2021-07-01 12:31:01	81
10	1001	9002	2021-07-01 12:01:01	(NULL)	(NULL)

找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名。由示例数据结果输出如下：

uid	exam_complete_cnt
1006	3

解释：用户1006近三个有作答试卷的月份为202109、202108、202106，作答试卷数为3，全部完成；用户1001近三个有作答试卷的月份为202109、202108、202107，作答试卷数为5，完成试卷数为4，因为有未完成试卷，故过滤掉。

示例1

输入:

drop table if exists exam_record;
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-06 10:01:01', '2021-09-06 10:21:02', 84),
(1006, 9001, '2021-08-02 12:11:01', '2021-08-02 12:31:01', 89),
(1006, 9002, '2021-06-06 10:01:01', '2021-06-06 10:21:01', 81),
(1006, 9002, '2021-05-06 10:01:01', '2021-05-06 10:21:01', 81),
(1006, 9001, '2021-05-01 12:01:01', null, null),
(1001, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1001, 9003, '2021-08-01 09:01:01', '2021-08-01 09:51:11', 78),
(1001, 9002, '2021-07-01 09:01:01', '2021-07-01 09:31:00', 81),
(1001, 9002, '2021-07-01 12:01:01', '2021-07-01 12:31:01', 81),
(1001, 9002, '2021-07-01 12:01:01', null, null);

输出:

1006|3

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6380KB, 提交时间: 2021-12-27


#近三个月有作答试卷的用户
#试卷的做答数，试卷完成数

select a.uid, count(a.start_time) as exam_complete_cnt
FROM
(
    select uid, start_time, submit_time,
    DENSE_RANK() over(partition by uid order by date_format(start_time, '%Y%m') desc) as time_rank
    from exam_record
) as a
where a.time_rank <= 3
group by a.uid
having count(a.start_time) = count(a.submit_time)
order by exam_complete_cnt desc, uid desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6416KB, 提交时间: 2022-01-28

with t as (select * from (
select uid,date_format(start_time,'%Y%m'),submit_time,dense_rank() over (partition by uid order by date_format(start_time,'%Y%m') desc) ranking from exam_record)t
where ranking<=3  )
select uid,count(*) exam_c
from t

group by uid
having  count(uid)=count(submit_time)
order by exam_c desc,uid desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6508KB, 提交时间: 2021-12-14

select uid, count(score) exam_complete_cnt
from
(select uid, DENSE_RANK() over(partition by uid order by DATE_FORMAT(start_time,'%Y%m')desc) mon, score
from exam_record) A
where mon <= 3
group by uid having count(uid) = count(score)# and count(mon)>=3
order by exam_complete_cnt desc, uid desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6508KB, 提交时间: 2021-11-30

select uid,count(score) as exam_complete_cnt from (
select uid,start_time,score,dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as recent_months
from exam_record 
    ) b
    where recent_months<=3
    group by uid
    having count(score)=count(uid)
    order by exam_complete_cnt desc,uid desc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6332KB, 提交时间: 2021-12-07

with temp as(
    select uid,exam_id,start_time,submit_time,score,
    dense_rank() over(partition by uid 
                      order by date_format(start_time,'%Y%m')
                     desc) ranking
from exam_record
)

select uid,count(start_time) exam_complete_cnt
from temp 
where ranking <= 3
group by uid
having count(score) = count(uid)
order by exam_complete_cnt desc,uid desc;

上一题

下一题

详情