33｜SQL 连表查询与聚合函数

数据通常分散在多张表中：服务器信息在 servers 表，所属项目在 projects 表，任务记录在 tasks 表。连表查询（JOIN）把相关数据从多张表组合到一起。聚合函数则对一组记录做统计：计数、求和、平均值。

一、表结构示例

CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

CREATE TABLE servers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hostname TEXT NOT NULL,
    project_id INTEGER,
    status TEXT DEFAULT 'running'
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    server_id INTEGER,
    action TEXT,
    created_at TEXT
);

servers.project_id 关联 projects.id，tasks.server_id 关联 servers.id。

二、JOIN 连表查询

INNER JOIN

只返回两张表中都匹配的记录：

cursor.execute("""
    SELECT s.hostname, p.name as project_name
    FROM servers s
    INNER JOIN projects p ON s.project_id = p.id
""")

如果某台服务器没有设置 project_id（或为 NULL），这条记录不会出现在结果中。

LEFT JOIN

返回左表的所有记录，右表不匹配时填 NULL：

cursor.execute("""
    SELECT s.hostname, p.name as project_name
    FROM servers s
    LEFT JOIN projects p ON s.project_id = p.id
""")

所有服务器都会出现在结果中，没有项目的那些行 project_name 为 NULL。

JOIN 类型	结果
INNER JOIN	只返回两表匹配的行
LEFT JOIN	返回左表所有行，右表不匹配填 NULL
RIGHT JOIN	返回右表所有行，左表不匹配填 NULL（SQLite 不支持）
FULL OUTER JOIN	返回两表所有行，不匹配填 NULL（SQLite 不支持）

SQLite 只支持 INNER JOIN 和 LEFT JOIN。需要 RIGHT JOIN 或 FULL JOIN 时，调整 SQL 写法或换数据库。

多表 JOIN

cursor.execute("""
    SELECT s.hostname, p.name, COUNT(t.id) as task_count
    FROM servers s
    LEFT JOIN projects p ON s.project_id = p.id
    LEFT JOIN tasks t ON t.server_id = s.id
    GROUP BY s.id
""")

三、聚合函数

# 统计服务器数量
cursor.execute("SELECT COUNT(*) FROM servers")

# 按状态分组统计
cursor.execute("""
    SELECT status, COUNT(*) as count
    FROM servers
    GROUP BY status
""")

# 有任务的服务器数量（DISTINCT 去重）
cursor.execute("SELECT COUNT(DISTINCT server_id) FROM tasks")

函数	作用
COUNT(*)	统计行数
COUNT(列)	统计该列非 NULL 的行数
SUM(列)	求和
AVG(列)	平均值
MAX(列)	最大值
MIN(列)	最小值

四、GROUP BY 与 HAVING

GROUP BY 按指定列分组，聚合函数对每个组单独计算：

cursor.execute("""
    SELECT project_id, COUNT(*) as server_count
    FROM servers
    WHERE status = 'running'
    GROUP BY project_id
    HAVING COUNT(*) >= 2
""")

子句	作用	执行时机
WHERE	过滤原始行	分组前
GROUP BY	按列分组	过滤后
HAVING	过滤分组结果	分组后

上面的 SQL：先过滤出 status = 'running' 的服务器，再按 project_id 分组，最后只保留服务器数量大于等于 2 的组。

五、子查询

子查询是嵌套在另一个查询中的查询：

# 查询有任务的服务器
cursor.execute("""
    SELECT * FROM servers
    WHERE id IN (SELECT DISTINCT server_id FROM tasks)
""")

# 查询每台服务器的最新任务
cursor.execute("""
    SELECT s.hostname, t.action, t.created_at
    FROM servers s
    JOIN tasks t ON t.id = (
        SELECT id FROM tasks
        WHERE server_id = s.id
        ORDER BY created_at DESC
        LIMIT 1
    )
""")

子查询可以出现在 SELECT、FROM、WHERE 中。复杂的子查询可能影响性能，必要时用 JOIN 替代。

六、常见错误

SELECT 中包含未聚合的非分组列

-- 错误：hostname 没有出现在 GROUP BY 中，结果不确定
SELECT hostname, status, COUNT(*) FROM servers GROUP BY status;

-- 正确：只选分组列和聚合函数
SELECT status, COUNT(*) FROM servers GROUP BY status;

-- 或把 hostname 也加入 GROUP BY
SELECT hostname, status, COUNT(*) FROM servers GROUP BY hostname, status;

SQLite 对这种写法比较宽松（返回每组的任意一行），但 MySQL/PostgreSQL 会报错。

WHERE 和 HAVING 混用

-- 错误：HAVING 不能过滤原始行
SELECT status, COUNT(*) FROM servers HAVING status = 'running' GROUP BY status;

-- 正确：原始行过滤用 WHERE
SELECT status, COUNT(*) FROM servers WHERE status = 'running' GROUP BY status;

JOIN 忘记 ON 条件

-- 错误：笛卡尔积，结果行数 = 表A行数 × 表B行数
SELECT * FROM servers JOIN projects;

-- 正确
SELECT * FROM servers JOIN projects ON servers.project_id = projects.id;