（十四）筛选、聚合与关联查询

1. 从筛选到排序与分组

-- 选择未读书目，先按标题排序，再取前三条。
SELECT id, title
FROM books
WHERE finished = 0
ORDER BY title, id
LIMIT 3;

-- 把阅读日志按书分组，留下总时长不少于 50 分钟的组。
SELECT book_id, SUM(minutes) AS total
FROM reading_log
GROUP BY book_id
HAVING SUM(minutes) >= 50
ORDER BY total DESC, book_id;

第一条先确定哪些行参与结果，再明确输出顺序和数量。第二条的 WHERE 可以筛选分组前的行，HAVING 则筛选已经聚合的组；AS total 给计算结果起一个列名，DESC 表示降序。

按前面 20、35、45 的日志，先形成 book_id=1 → [20,35] 与 book_id=2 → [45]，求和得到 55、45，最后只留下 55。这是理解结果的逻辑处理顺序，数据库实际执行时还会根据索引与计划安排读取。

2. `NULL` 与空结果

# NULL 表示这里没有数值；COUNT(*) 计行，COUNT(minutes) 只计非 NULL 值。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    with connection:
        connection.execute("CREATE TABLE records(minutes INTEGER)")
        connection.executemany("INSERT INTO records VALUES (?)", [(None,), (0,), (35,)])
    row = connection.execute("SELECT COUNT(*), COUNT(minutes), SUM(minutes), AVG(minutes) FROM records").fetchone()
    print(row)
    print(connection.execute("SELECT COUNT(*) FROM records WHERE minutes IS NULL").fetchone()[0])

输出 (3, 2, 35, 17.5) 与 1。0 是已有数值，NULL 则没有参与这次求和与平均。检查缺失值写 IS NULL 或 IS NOT NULL，minutes = NULL 不会产生通常期待的相等判断。COALESCE(value, 0) 可以在结果缺失时给出显示用的零。

3. `JOIN` 把关联记录接起来

# LEFT JOIN 保留没有日志的书，匹配不到的日志字段得到 NULL。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    with connection:
        connection.execute("CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT)")
        connection.execute("CREATE TABLE reading_log(id INTEGER PRIMARY KEY, book_id INTEGER, minutes INTEGER)")
        connection.executemany("INSERT INTO books VALUES (?, ?)", [(1, "Python"), (2, "NumPy"), (3, "Pygame")])
        connection.executemany("INSERT INTO reading_log(book_id, minutes) VALUES (?, ?)", [(1, 20), (1, 35), (2, 45)])
    rows = connection.execute("""
        SELECT b.title, COUNT(l.id), COALESCE(SUM(l.minutes), 0)
        FROM books AS b LEFT JOIN reading_log AS l ON l.book_id = b.id
        GROUP BY b.id, b.title
        ORDER BY b.id
    """).fetchall()
    print(rows)

得到 [('Python', 2, 55), ('NumPy', 1, 45), ('Pygame', 0, 0)]。普通 INNER JOIN 只保留匹配的行；这里 LEFT JOIN 还保留没有日志的 Pygame。用 COUNT(l.id) 计算匹配到的日志数，不能把保留下来的那一行空匹配也算成一次阅读。

4. 两组一对多关联会放大行数

同一本书有两条日志，又有两个标签，直接同时连接两张子表，会形成四个组合：

# 先观察重复连接导致的两倍求和，再用 EXISTS 只判断标签是否存在。
from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
    with connection:
        connection.execute("CREATE TABLE logs(book_id INTEGER, minutes INTEGER)")
        connection.execute("CREATE TABLE tags(book_id INTEGER, tag TEXT)")
        connection.executemany("INSERT INTO logs VALUES (?, ?)", [(1, 20), (1, 35)])
        connection.executemany("INSERT INTO tags VALUES (?, ?)", [(1, "基础"), (1, "语法")])
    wrong = connection.execute("SELECT SUM(l.minutes) FROM logs AS l JOIN tags AS t ON t.book_id = l.book_id").fetchone()[0]
    correct = connection.execute("""
        SELECT SUM(l.minutes) FROM logs AS l
        WHERE EXISTS (SELECT 1 FROM tags AS t WHERE t.book_id = l.book_id)
    """).fetchone()[0]
    print(wrong, correct)

输出 110 55。EXISTS 只判断子查询是否找到一行，没有把每个标签复制进外层结果。也可以先按书籍聚合日志，再连接已经每书一行的结果。先画出连接产生的中间行，再决定在哪里求和，比看到最终数字后再加 DISTINCT 更容易找出原因。SQL 查询处理