SQLite 教学工作区 · 课程 0003 · 约 25 分钟 · 前置:0002 · 2026-09-24

把两张表接起来回答一个真问题

为什么是这一步

0002 你在建表;这一节开始问表。使命里那句「写查询」到这里才真正开始 —— 而查询的价值不在语法,在于它能回答一个你本来只能猜的问题。 下面两个问题都是从这个 321 MB 真库里跑出来的,不是编的示例。

第一步:聚合 —— 单表也能回答"分布"

sqlite3 -readonly /tmp/sqlite-练习/ctx.db

SELECT project_path, category, count(*) AS n
FROM   memories
GROUP  BY project_path, category
ORDER  BY n DESC
LIMIT  5;

真实输出(你的库,2026-09-24):

project_path                                  category       n
--------------------------------------------  -------------  --
git:13097390f1a057ee154a1591e68fc699dc06a632  ARCHITECTURE   57
git:13097390f1a057ee154a1591e68fc699dc06a632  CONFIG_VALUES  51
git:13097390f1a057ee154a1591e68fc699dc06a632  CONSTRAINTS    49
dir:89720ded51fa                              CONFIG_VALUES  41
git:08ad12f9f6e7cf164a9dcf52a1c0fcfd8e4014f2  CONFIG_VALUES  25

GROUP BY 只要回答三个问题就写完了:

1. 按什么分组? project_path, category —— 分组键写几列,就是几层分组
2. 每组算什么? count(*) —— 也可以是 sum() / avg() / min() / max()
3. 怎么排、取几行? ORDER BY n DESC LIMIT 5 —— 不排序的话,顺序是未定义的

一个必须现在就分清的点:count(*) 数的是行数, count(某列) 数的是该列非空的行数。这两者在有 NULL 的列上结果不同 —— 这也是 count 最常被写错的地方。

第二步:JOIN —— 两表接起来才有"谁花了多少"

上面那张表只有 project_path 这种指纹,读起来不直观。而另一张表 tags 里 (67,313 行)记着每次对话消耗的 tag 与 token,它用 session_id 标识会话; session_projects 表(663 行)则回答「这个会话是在哪个项目上跑的」。把两张表接起来:

SELECT sp.project_path,
       count(*)            AS tags,
       sum(t.token_count)  AS tokens
FROM   tags t
JOIN   session_projects sp ON sp.session_id = t.session_id
GROUP  BY sp.project_path
ORDER  BY tokens DESC
LIMIT  5;

真实输出:

project_path                                  tags   tokens
--------------------------------------------  -----  --------
git:13097390f1a057ee154a1591e68fc699dc06a632  23022  12971694
git:08ad12f9f6e7cf164a9dcf52a1c0fcfd8e4014f2  6728   7347492
dir:89720ded51fa                              11138  3923164
git:aa60bb95737d9aa91300434284d4c7e06c08b2d1  6341   3716019
dir:3e9fa1cb9afe                              9228   2145190

第一行就是你正在读的这个笔记库:23,022 个 tag、12,971,694 个 token。 这不是"练习数据",这是你自己的工作痕迹。

读一条 JOIN 的四步(照着念一遍就会写)

1. 左表是谁? FROM tags t —— 我关心的主体(消耗记录)
2. 右表是谁? JOIN session_projects sp —— 我要补的信息(会话属于哪个项目)
3. 用什么键接? ON sp.session_id = t.session_id —— 两边都有的那一列
4. 接完一行代表什么? 一条 tag 记录 + 它所属会话的项目 —— 这一步决定你能不能正确聚合

接之前先查一次右表的连接键唯不唯一,这是老手习惯:

SELECT count(*) AS rows, count(DISTINCT session_id) AS uniq FROM session_projects;
-- 663 | 663   → 唯一,不会把行数放大

如果右边一个键对应多行,JOIN 会让左表的行"翻倍",sum() 立刻算错 —— 而且不报任何错。

第三步(彩蛋):同一张表,两种写法,一种走索引

0004 会专门练,这里先看一眼"数据库怎么读你的查询":

EXPLAIN QUERY PLAN
SELECT * FROM memories WHERE project_path = 'x' AND status = 'active';

EXPLAIN QUERY PLAN
SELECT * FROM memories WHERE category = 'ARCHITECTURE';

-- 第一条:
`--SEARCH memories USING INDEX idx_memories_project_status_expires (project_path=? AND status=?)

-- 第二条:
`--SCAN memories

SEARCH … USING INDEX = 用索引直接跳过去;SCAN = 逐行看完整张表。 差别不在"结果对不对",而在代价:这一节的两条查询都跑得动, 但如果表长到一亿行,第二种写法会让你等很久。

自测(选项一样长,别从长度上猜)