实例：错题本（Mistake）｜技术：CASE WHEN 更新、条件计数、复习队列

一、错题查询的三层技术

错题本的数据层引入了前面实例没有的两类 SQL 高级技巧：CASE WHEN 条件更新（reviewOnce）与 SUM(CASE WHEN) 条件计数（overview）。本篇文章逐一深入，重点讲 SQL 表达式的引用旧值更新 与 条件聚合计数。

二、reviewOnce：CASE WHEN 条件更新（核心）

复习一次要同时做三件事：轮次 +1、刷新复习时间、满 5 次自动掌握：

static async reviewOnce(context: common.Context, id: number): Promise<void> {
  const store = await MistakeDao.getStore(context);
  const now = Date.now();
  await store.executeSql(
    `UPDATE ${MistakeDao.TABLE}
     SET rounds = rounds + 1,
         review_time = ${now},
         mastered = CASE WHEN rounds + 1 >= 5 THEN 1 ELSE mastered END
     WHERE id = ${id}`
  );
}

UPDATE mistake
SET rounds = rounds + 1,
    review_time = 1720869600000,
    mastered = CASE WHEN rounds + 1 >= 5 THEN 1 ELSE mastered END
WHERE id = 3;

逐行拆解：

1. rounds = rounds + 1 自增表达式：SET 的右侧引用旧值——SQL 的 SET 求值时 rounds 是更新前的值（先读旧值算新值再写入）。**「UPDATE 里引用旧值」**是 SQL 的天然能力（RdbPredicates 的 values 做不到——只能设常量）。

2. mastered = CASE WHEN rounds + 1 >= 5 THEN 1 ELSE mastered END：CASE 表达式根据「新轮次」决定新状态——rounds + 1（复习后的轮次）≥ 5 → 1（掌握），否则保持原值。「CASE 在 UPDATE 里做条件分支赋值」——不用先查再改（避免竞态 + 少一次往返）。

3. 三条 SET 一条 WHERE：一个 UPDATE 原子完成三件事——「一次更新多字段 + 条件逻辑」。

为什么必须 executeSql？ RdbPredicates 的 update(values, predicates) 的 values 只能放固定值（{ rounds: ??? }——不知道当前值）；自增（rounds + 1）与条件（CASE）是表达式——**「需要引用旧值/条件计算的更新，用 executeSql 写原生 SQL」**是 API 选择准则。

掌握的门槛语义：复习 5 次（rounds 达到 5）自动掌握——「复习 5 次 = 掌握」的简化间隔重复。页面 Toast 的 m.rounds + 1 >= 5 与 SQL 的 rounds + 1 >= 5 逻辑一致（页面算展示文案）。

页面调用：

async doReview(m: Mistake): Promise<void> {
  await MistakeDao.reviewOnce(this.context, m.id);
  this.detailVisible = false;
  await this.refresh();
  promptAction.showToast({ message: m.rounds + 1 >= 5 ? '🎉 复习 5 次，已标记掌握！' : `✅ 复习第 ${m.rounds + 1} 次` });
}

「DAO 更新 + 页面 refresh」：reviewOnce 只做更新，页面 refresh 重查（轮次变化反映到列表与统计）——「写操作与读操作分离，刷新驱动 UI」。

三、overview：SUM(CASE WHEN) 条件计数

总览统计要一次拿到四个数字（总数/未掌握/已掌握/待复习）：

static async overview(context: common.Context): Promise<MistakeOverview> {
  const store = await MistakeDao.getStore(context);
  const result = await store.querySql(
    `SELECT COUNT(*) AS total,
            SUM(CASE WHEN mastered=0 THEN 1 ELSE 0 END) AS pending,
            SUM(CASE WHEN mastered=1 THEN 1 ELSE 0 END) AS mastered,
            SUM(CASE WHEN mastered=0 AND rounds<5 THEN 1 ELSE 0 END) AS to_review
     FROM ${MistakeDao.TABLE}`
  );
  let total = 0, pending = 0, mastered = 0, toReview = 0;
  if (result.goToNextRow()) {
    total = result.getLong(result.getColumnIndex('total'));
    pending = result.getLong(result.getColumnIndex('pending'));
    mastered = result.getLong(result.getColumnIndex('mastered'));
    toReview = result.getLong(result.getColumnIndex('to_review'));
  }
  result.close();
  const stats: MistakeOverview = { total: total, pending: pending, mastered: mastered, toReview: toReview };
  return stats;
}

SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) 的语义：对每一行求 CASE 表达式（条件真 → 1、假 → 0），SUM 累加——等于「满足条件的行数」。即：

统计	条件	含义
total	（无条件）COUNT(*)	全部错题数
pending	mastered=0	未掌握数
mastered	mastered=1	已掌握数
to_review	mastered=0 AND rounds<5	待复习数

与四次 COUNT + WHERE 的对比：

-- 条件计数（1 次查询）
SELECT COUNT(*) AS total, SUM(CASE WHEN mastered=0 THEN 1 ELSE 0 END) AS pending ... FROM mistake;
-- 四次查询（4 次往返）
SELECT COUNT(*) FROM mistake;
SELECT COUNT(*) FROM mistake WHERE mastered=0;
SELECT COUNT(*) FROM mistake WHERE mastered=1;
SELECT COUNT(*) FROM mistake WHERE mastered=0 AND rounds<5;

「一次扫描多条件计数」vs「四次扫描」——SUM(CASE WHEN) 是单次全表扫描内完成多组计数（一次往返、一列一统计）。**「条件计数用 SUM(CASE WHEN)」**是聚合查询的高级技巧（前面的 GROUP BY 按维度分行，这里按条件分列）。

与 GROUP BY 的对比：

需求	技术	结果形态
每错因数量	GROUP BY reason	多行（每错因一行）
各状态数量	SUM(CASE WHEN)	多列（每状态一列）

「分组统计要行、固定状态计数要列」——按数据形态选技术。

四、queryReviewQueue：复习队列

「待复习」的队列定义（最久没看的优先）：

static async queryReviewQueue(context: common.Context): Promise<Mistake[]> {
  const store = await MistakeDao.getStore(context);
  const predicates = new relationalStore.RdbPredicates(MistakeDao.TABLE);
  predicates.equalTo('mastered', 0).lessThan('rounds', 5).orderByAsc('review_time');
  const result = await store.query(predicates);
  return MistakeDao.collect(result);
}

SELECT * FROM mistake
WHERE mastered = 0 AND rounds < 5
ORDER BY review_time ASC;

三要素：

1. mastered = 0：未掌握（已掌握的不复习）；
2. rounds < 5：轮次未满（复习满 5 次的已掌握，无需复习）；
3. ORDER BY review_time ASC：最久没复习的排最前（review_time 是上次复习时间——时间戳小 = 早 = 排前）。

**「未掌握 + 未满轮 + 最旧优先」**是复习队列的完整定义——间隔重复的简化版（真实 Anki 按间隔天数排序，这里按「多久没复习」排序）。

queryAll 的排序：orderByAsc('mastered').orderByAsc('rounds').orderByDesc('created_time')——未掌握在前、轮次少在前、新录入在前——「待加强的错题优先展示」的三键排序。

五、queryByKnowledge：知识点 LIKE 搜索

static async queryByKnowledge(context: common.Context, knowledge: string): Promise<Mistake[]> {
  const store = await MistakeDao.getStore(context);
  const predicates = new relationalStore.RdbPredicates(MistakeDao.TABLE);
  predicates.like('knowledge', `%${knowledge}%`).orderByDesc('created_time');
  const result = await store.query(predicates);
  return MistakeDao.collect(result);
}

知识点模糊搜索（与实例 16 的 LIKE 同构）——搜「函数」命中「函数变换」标签的错题——「同类知识点一起复习」。

六、技术要点对照表

技术点	实现方式	生产价值
自增更新	rounds = rounds + 1	引用旧值
条件赋值	CASE WHEN 满 5 掌握	无往返流转
条件计数	SUM(CASE WHEN)	一次多统计
复习队列	双条件 + review ASC	最旧优先
三键排序	mastered + rounds + created	待加强在前
知识点搜索	LIKE %kw%	关联复习

七、常见问题 FAQ

Q1：rounds + 1 在 SET 里为什么能引用旧值？
A：SQL 的 UPDATE 求值语义——SET 右侧表达式基于更新前的行值计算（rounds + 1 先读旧 rounds 再加 1 写入）。这是 SQL 标准行为（区别于编程语言的「+=」需要先读）。**「SQL 的 SET 表达式天然引用旧值」**是自增更新的基础。

Q2：CASE WHEN 和 IF 有什么区别？
A：SQLite 用 CASE WHEN 条件 THEN 值 ELSE 值 END（标准 SQL）；IF() 是 MySQL 方言（SQLite 也支持 IF 但 CASE 更标准）。「条件赋值用 CASE WHEN」——跨数据库可移植。

Q3：为什么 overview 不用四次 COUNT？
A：四次 COUNT 是四次查询（四次往返）；SUM(CASE WHEN) 一次扫描一次往返拿到四个统计。数据量大时差异显著（单次扫描 vs 四次扫描）。**「能一次扫描就一次扫描」**是聚合性能原则。

Q4：SUM(CASE WHEN mastered=1 THEN 1 ELSE 0 END) 能简写吗？
A：SUM(mastered) 也行（mastered 本身就是 0/1，求和即计数）——但语义不清晰（读代码不知道在计数）；CASE 版本自解释。「显式 CASE 换可读性」，教学选显式。

Q5：复习队列的「最旧优先」为什么用 ASC？
A：review_time 是「上次复习时间戳」——越早复习的戳越小，ASC 让「最久没复习」的排最前（数字最小在前）。「时间升序 = 最旧优先」（与创建时间 DESC 的「最新在前」方向相反，语义不同）。

Q6：markMastered 手动掌握和 reviewOnce 自动掌握冲突吗？
A：不冲突——手动（markMastered 直接设 mastered）与自动（reviewOnce 满 5 次 CASE 置 1）是两条通道；手动取消掌握（1→0）后该题重新进复习队列（rounds 保留）——「手动干预覆盖自动」，轮次保留让下次自动掌握更快。

Q7：review_time 初始值为什么是 createdTime？
A：种子数据 review_time: s.createdTime——录入时间当上次复习时间（刚录入 = 刚「看过」）——这样队列排序从录入时刻算「多久没看」，合理。**「初始复习时间 = 创建时间」**是队列语义的合理默认。

八、文章小结

本篇文章深入讲解了错题本的数据层核心：CASE WHEN 条件更新（rounds 自增 + 满 5 自动掌握的原子更新，executeSql 写表达式）、SUM(CASE WHEN) 条件计数（一次扫描四统计，与 GROUP BY 的「行 vs 列」对比）、复习队列（未掌握 + 轮次未满 + 最旧优先）、三键排序（待加强在前）。核心心法是「SQL 表达式引用旧值」与「条件计数用 SUM(CASE WHEN)」——两个可迁移到任何「计数 + 状态流转」场景的高级技巧。

下一篇（19-4）展示 10 道错题四科种子数据，让错题本一开屏就有复习队列与错因统计。

九、reviewOnce 执行细节：SET 表达式的求值顺序

UPDATE 的 SET 子句有一个常被忽略的语义：同一行的所有 SET 右侧表达式都在「更新前的旧行快照」上求值，全部算完才统一写入——不是从左到右串行「改一列再算下一列」。

以 rounds=4 的错题执行 reviewOnce 为例：

步骤	求值内容	rounds 快照
1	读取旧行快照	4（旧值）
2	rounds + 1 算出新轮次 5	4
3	CASE 中 rounds + 1 >= 5（5≥5）成立 → mastered=1	4
4	统一写入	rounds=5、mastered=1

关键点：第 2、3 步读到的 rounds 都是旧值 4——所以 SET 里的 rounds + 1 与 CASE 里的 rounds + 1 计算结果一致（都是 5），「本次复习后轮次 ≥ 5 即掌握」的判断才准确。若 SQL 引擎逐列串行求值，CASE 就会读到新 rounds（5），任何基于旧值的条件都会算错——理解快照语义是写对条件更新的前提（这与编程语言 rounds += 1 后再读是「新值」完全不同）。

十、SUM(CASE WHEN) 与 GROUP BY：场景选择对比

两者都做「按条件统计」，但产出形态与适用场景不同：

维度	SUM(CASE WHEN)	GROUP BY
适用	状态集合固定、已知	维度取值未知、可变
结果形态	一行多列	一行一个取值
新增取值	需改 SQL 加一列	自动多一行
代表场景	总览（掌握/未掌握/待复习）	错因统计、学科统计
查询往返	1 次	1 次

错题本里的分工：overview 的掌握状态只有 0/1 两种且固定 → 用 SUM(CASE WHEN) 分列；错因（reason）可能新增（学生可自定义）→ 用 GROUP BY 分行，新错因自动出现在统计里。「固定状态用列、可变维度用行」——统计结构跟着数据形态走。

十一、复习队列的排序语义：时间升序为什么是「最旧优先」

SELECT * FROM mistake
WHERE mastered = 0 AND rounds < 5
ORDER BY review_time ASC, rounds ASC, id ASC;

review_time 是「上次复习时间」——时间戳越小 = 越久没复习 = 遗忘风险越高，所以 ASC（升序）让最旧的排最前。这是简化间隔重复：真实 Anki 按「下次到期时间」排序（需要 interval/due 字段），本实例只有 review_time 一个时间字段，用「多久没看」近似「该复习了」。

两个容易混淆的排序语义：

排序	方向	语义
复习队列 review_time	ASC	最久没复习在前（越旧越急）
列表 created_time	DESC	最新录入在前（越新越显眼）

当前代码只有 orderByAsc('review_time') 一个排序键；同日录入（同时间戳）时顺序不稳定，可加 rounds ASC（轮次少的优先）与 id ASC 作稳定 tie-breaker——这是生产环境值得补的细节。

十二、FAQ（补充）

Q1：UPDATE 的多个 SET 表达式求值有先后顺序吗？
A：SQLite 中同一行所有 SET 右侧表达式基于旧行快照统一求值，没有先后——都读旧值。这与编程语言 rounds += 1 后再读 rounds 得到新值完全不同。**「SET 全体引用旧值」**是写条件更新的前提。

Q2：CASE 里为什么写 rounds + 1 而不是 rounds？
A：rounds 是旧值——若写 rounds >= 5，则第 4 次复习时（旧值 4）不满足、第 5 次复习（旧值 5）才掌握，等于多复习一次。写 rounds + 1 表示「本次复习后的轮次」，与页面 Toast 的 m.rounds + 1 >= 5 一致：复习满 5 次即掌握。

Q3：复习队列为什么不直接存「下次复习时间」字段？
A：简化设计——只有 review_time，用「上次复习距今多久」近似到期判断。真实间隔重复（Anki）有 interval（间隔天数）与 due（到期时间），本实例用单时间字段降复杂度。**「单时间字段的简化队列」**是教学取舍。

Q4：SUM(CASE WHEN) 列太多可以换 GROUP BY 吗？
A：可以——但 GROUP BY 返回多行，页面要逐行映射列名，代码更绕；固定状态集合下「一行多列」直接 getLong 取列最省事。「列数固定用 CASE 分列，取值可变用 GROUP BY 分行」。