统计有未完成状态的试卷的未完成数和未完成率

现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），数据如下：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1001	9001	2021-05-02 10:01:01	2021-05-02 10:30:01	81
3	1001	9001	2021-09-02 12:01:01	(NULL)	(NULL)

请统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate。由示例数据结果输出如下：

exam_id	incomplete_cnt	complete_rate
9001	1	0.333

解释：试卷9001有3次被作答的记录，其中两次完成，1次未完成，因此未完成数为1，未完成率为0.333（保留3位小数）

示例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
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-09-02 12:01:01', null, null);

输出:

9001|1|0.333

原站题解

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

Mysql 解法, 执行用时: 36ms, 内存消耗: 6404KB, 提交时间: 2022-02-10

select exam_id,sum(case when score is null then 1 else 0 end) incomplete_cnt,
round((count(*)-count(submit_time))/count(*),3) incomplete_rate 
from exam_record
group by exam_id
HAVING (count(*)-count(submit_time))<>0

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


select exam_id, sum(if(submit_time is null, 1, 0)) as imcomplete_cnt, 
round(sum(if(submit_time is null, 1, 0)) / count(start_time), 3) as imcomplete_rate
from exam_record
group by exam_id
having sum(if(submit_time is null, 1, 0)) > 0

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

SELECT
     exam_id
    ,sum(case when score is null then 1 else 0 end) incomplete_cnt
    ,round(sum(case when score is null then 1 else 0 end) / count(*),3) incomplete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt > 0

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

select *
from(
select exam_id,count(start_time)-count(score) incomplete_cnt
,round((count(start_time)-count(score))/count(start_time),3) ir
from exam_record
group by exam_id) a
where ir != 0.000

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

select exam_id,
    sum(case when submit_time is null then 1 else 0 end) as incomplete_cnt,
    round(sum(case when submit_time is null then 1 else 0 end)/count(id),3) as incomplete_rate
from exam_record
group by exam_id
having incomplete_cnt >=1

上一题

下一题

详情