在线教育错题本功能怎么搭？错题记录、归类与重做的 4 个要点

CREATE TABLE wrong_question (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  student_id BIGINT NOT NULL,
  question_id BIGINT NOT NULL,
  subject_id INT NOT NULL,
  wrong_reason VARCHAR(50) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uk_student_question (student_id, question_id)
);

CREATE TABLE wrong_review (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  wrong_id BIGINT NOT NULL,
  review_result TINYINT NOT NULL,
  reviewed_at DATETIME NOT NULL
);

SELECT q.subject_id, q.wrong_reason,
       COUNT(*) AS wrong_cnt,
       SUM(r.review_result) / COUNT(r.id) AS pass_rate
FROM wrong_question q
LEFT JOIN wrong_review r ON r.wrong_id = q.id
WHERE q.status != 2
GROUP BY q.subject_id, q.wrong_reason
HAVING wrong_cnt >= 3 AND pass_rate < 0.5;

function handleReview(wrong, result) {
  saveReview({ wrongId: wrong.id, reviewResult: result, reviewedAt: now });
  if (result === 1) {
    wrong.consecutivePass++;
    if (wrong.consecutivePass >= 2) { wrong.status = 1; }
    else { wrong.nextReviewAt = calcNext(wrong, 3); }
  } else {
    wrong.wrongCount++;
    wrong.consecutivePass = 0;
    wrong.nextReviewAt = calcNext(wrong, 1);
  }
  saveWrong(wrong);
}