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

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

SQL案例：统计有未完成状态的试卷的未完成数和未完成率（三种写法）_数据库

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

SQL案例：统计有未完成状态的试卷的未完成数和未完成率（三种写法）_sql_02

第一种写法

  • 解释：
    试卷 9001 有 3 次被作答的记录，其中两次完成，1 次未完成，因此未完成数为 1，未完成率为 0.333（保留 3 位小数）
  • 思路：这题只需要注意一个是有条件限制，一个是没条件限制的；要么分别查询条件，然后合并；要么直接在 select 里面进行条件判断。
  • 答案：
SELECT exam_id,
       count(submit_time IS NULL) incomplete_cnt,
       ROUND(count(submit_time IS NULL) / count(*), 3) complete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt <> 0
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

对符合条件的才COUNT，这个having解释一下， 无论是 complete_rate 还是 incomplete_cnt，只要不为 0 即可，不为 0 就意味着有未完成的。

第二种写法

SELECT 
    COUNT(*) AS incomplete_cnt,
    (COUNT(*) / (SELECT COUNT(*) FROM exam_record)) AS incomplete_rate
FROM 
    exam_record
WHERE 
    submit_time IS NULL;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

这个查询首先从 exam_record 表中选择所有没有提交时间（即未完成）的记录。然后，它计算这些记录的数量（incomplete_cnt），并将这个数量除以总记录数（通过在子查询中计算总记录数）得到未完成率（incomplete_rate）。注意，这里我们假设所有的考试都有开始时间，如果没有开始时间的考试也应该被视为未完成，则需要将条件修改为 submit_time IS NULL AND start_time IS NOT NULL。

第三种写法

SET @total_exams := (SELECT COUNT(*) FROM exam_record);
SET @incomplete_exams := (SELECT COUNT(*) FROM exam_recordWHERE submit_time IS NULL);

SELECT 
    @incomplete_exams AS incomplete_cnt,
    IF(@total_exams > 0, (@incomplete_exams / @total_exams), 0) AS incomplete_rate;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

这个查询首先设置两个变量 @total_exams 和 @incomplete_exams 分别表示总试卷数量和未完成的试卷数量。然后，它使用 IF 函数检查总试卷数量是否大于0，如果是，则计算未完成率；否则，返回0。