AI 写的 LEFT JOIN 少了项目:把筛选位置和计数一起查

小周debug Lv.3 2026/11/9 13:32:13 浏览 0

让 AI 帮忙写统计 SQL 时,如果要求“所有项目都列出,没有未关闭工单的显示 0”,看到 LEFT JOIN 还不够:右表筛选放在 WHERE,没有匹配行的项目仍可能被过滤掉。

下面是一个可直接运行的对照:Atlas 有 2 条 open 和 1 条 closed;Birch 只有 1 条 closed;Cedar 没有工单。前提是 Python 3 自带 sqlite3,无需数据库服务。把代码存为 demo.py 后运行 python3 demo.py;也可以下载文末示例包解压运行。脚本只建内存数据库,不会修改已有数据。

import sqlite3

con = sqlite3.connect(':memory:')
con.executescript('''
CREATE TABLE projects (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE tickets (id INTEGER PRIMARY KEY, project_id INTEGER NOT NULL, status TEXT NOT NULL);
INSERT INTO projects VALUES (1, 'Atlas'), (2, 'Birch'), (3, 'Cedar');
INSERT INTO tickets VALUES (1, 1, 'open'), (2, 1, 'open'),
                           (3, 1, 'closed'), (4, 2, 'closed');
''')

queries = {
    'WHERE filter': '''
        SELECT p.name, COUNT(t.id)
        FROM projects AS p LEFT JOIN tickets AS t ON t.project_id = p.id
        WHERE t.status = 'open'
        GROUP BY p.id, p.name ORDER BY p.id
    ''',
    'ON filter': '''
        SELECT p.name, COUNT(t.id)
        FROM projects AS p LEFT JOIN tickets AS t
          ON t.project_id = p.id AND t.status = 'open'
        GROUP BY p.id, p.name ORDER BY p.id
    ''',
    'ON filter + COUNT(*)': '''
        SELECT p.name, COUNT(*)
        FROM projects AS p LEFT JOIN tickets AS t
          ON t.project_id = p.id AND t.status = 'open'
        GROUP BY p.id, p.name ORDER BY p.id
    ''',
}
for label, sql in queries.items():
    rows = con.execute(sql).fetchall()
    print(label + ':', rows)
con.close()

预期输出:

WHERE filter: [('Atlas', 2)]
ON filter: [('Atlas', 2), ('Birch', 0), ('Cedar', 0)]
ON filter + COUNT(*): [('Atlas', 2), ('Birch', 1), ('Cedar', 1)]

第一条把 t.status = 'open' 放到 WHERE:左连接补出来的右侧字段为 NULL,条件不成立,因此没有 open 工单的项目也被排除。第二条把它写进 ON,限定的是能匹配的工单,左表项目仍保留;COUNT(t.id) 不计算补出来的 NULL。第三条虽然保留了项目,COUNT(*) 却会把补出来的那一行也数上,所以两个 0 都变成了 1。这里使用的 t.id 是非空主键;如果换成允许 NULL 的字段,就不能把它的计数直接等同于匹配条数。

检查 AI 生成的查询时,先写下应保留哪些左表行,再拿“只有不符合条件的右表行”和“完全没有右表行”各测一次。若业务本来就要排除没有 open 工单的项目,WHERE 过滤可能正是需要的行为;关键是与需求对齐。这个固定样例已用 Python 3.12 和 SQLite 3.53.1 本地运行,结果只证明示例查询,不代表模型或生产数据库的实测。

下载完整示例包

评论 0

发表评论

登录后参与讨论

登录

暂无评论