SQLite中Count统计与子查询问题：合著者计数错误排查

我完全懂你踩的这个坑——同一作者同一篇论文因为关联多个机构，在writtenby表中生成了多条重复记录，直接统计的时候这些重复行就会搞乱计数，哪怕用了DISTINCT如果没用到点子上也没用。当然可以用子查询解决，而且思路很直接，咱们一步步理清楚：

问题根源 #

writtenby表中，同一paper_id+author_id会对应多条记录（不同inst_id），当你直接关联表查询时，这些重复行会让整个结果集“膨胀”，最后统计合著者数量时，要么把目标作者的重复记录也算进总数，要么因为结果集重复导致统计逻辑出错。

子查询解决方案 #

核心思路是：先提取目标作者参与的所有唯一论文ID（避免同一论文被多次处理），再针对每篇论文统计其唯一的作者数量（排除目标作者自己就是合著者数量）。

方案1：使用相关子查询统计 #

-- 替换?为你要查询的目标author_id
SELECT 
    p.doi,
    p.year,
    -- 统计该论文的所有唯一作者数，减1排除目标作者自己
    (SELECT COUNT(DISTINCT w.author_id) FROM writtenby w WHERE w.paper_id = p.paper_id) - 1 AS co_author_count
FROM papers p
-- 子查询先拿到目标作者参与的所有唯一论文ID
JOIN (
    SELECT DISTINCT paper_id FROM writtenby WHERE author_id = ?
) target_papers ON p.paper_id = target_papers.paper_id
ORDER BY p.year DESC, p.doi;

方案2：用CTE预处理论文作者数（更清晰） #

如果你的SQLite版本支持CTE（3.8.3及以上都支持），可以用公共表达式先预处理每篇论文的总作者数，再关联查询：

-- 替换?为目标author_id
WITH paper_total_authors AS (
    SELECT 
        paper_id,
        COUNT(DISTINCT author_id) AS total_authors
    FROM writtenby
    GROUP BY paper_id
)
SELECT 
    p.doi,
    p.year,
    pta.total_authors - 1 AS co_author_count
FROM papers p
JOIN (
    SELECT DISTINCT paper_id FROM writtenby WHERE author_id = ?
) target_papers ON p.paper_id = target_papers.paper_id
JOIN paper_total_authors pta ON p.paper_id = pta.paper_id
ORDER BY p.year DESC, p.doi;

为什么之前的查询出错？ #

举个例子，如果你之前的查询是直接关联writtenby后分组统计，没有先去重目标作者的论文：

-- 错误示例：未去重目标作者的论文记录
SELECT p.doi, p.year, COUNT(DISTINCT w.author_id)
FROM papers p
JOIN writtenby w ON p.paper_id = w.paper_id
WHERE w.author_id = ?
GROUP BY p.doi, p.year;

这时虽然用了DISTINCT author_id，但如果还关联了inst或authors表，会导致结果集进一步膨胀（每个作者的每个机构都生成一行），最终让统计逻辑偏离预期。而我们的子查询先把目标作者的论文去重，从根源上避免了结果集膨胀。

内容的提问来源于stack exchange，提问作者fraz