SQLite如何对3个附加数据库的UNION ALL查询结果求和

你现有写法通过UNION ALL返回三个独立库的单计数值，不需要在应用层额外做求和计算，直接在SQL层面就可以高效得到总和，推荐以下几种实现方式：

• 最小改动方案：嵌套子查询聚合
  完全复用你原有的查询逻辑，只需要把UNION ALL返回的三行结果作为子表，外层用SUM()函数直接求和，改造成本最低，不容易写错。

  ATTACH 'database1.s3db' AS DB1;
  ATTACH 'database2.s3db' AS DB2;
  ATTACH 'database3.s3db' AS DB3;
  
  SELECT SUM(cnt) AS total_answer_count
  FROM (
      SELECT count(DB1.tblPool.AnswerR2) AS cnt
        FROM DB1.tblPool
       WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' 
         AND CorrectAns = 'A2' 
         AND DB1.tblPool.AnswerR2 != 'NA'
      UNION ALL
      SELECT count(DB2.tblPool.AnswerR2) AS cnt
        FROM DB2.tblPool
       WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' 
         AND CorrectAns = 'A2' 
         AND DB2.tblPool.AnswerR2 != 'NA'
      UNION ALL
      SELECT count(DB3.tblPool.AnswerR2) AS cnt
        FROM DB3.tblPool
       WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' 
         AND CorrectAns = 'A2' 
         AND DB3.tblPool.AnswerR2 != 'NA'
  );
  

• 更高性能方案：标量子查询直接相加
  省去UNION ALL的结果集合并步骤，三个库的计数分别作为独立标量子查询，直接做算术相加，SQLite会分别对三个表走索引扫描，执行效率更高，逻辑也更直观。

  ATTACH 'database1.s3db' AS DB1;
  ATTACH 'database2.s3db' AS DB2;
  ATTACH 'database3.s3db' AS DB3;
  
  SELECT
    (SELECT count(AnswerR2) FROM DB1.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA')
    +
    (SELECT count(AnswerR2) FROM DB2.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA')
    +
    (SELECT count(AnswerR2) FROM DB3.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA')
  AS total_answer_count;
  

• 兼顾明细核对方案：同时返回单库计数和总计
  如果需要同时核对每个库的单独计数和最终总和，可以在结果集最后追加一行总计，方便校验数据是否正确：

  ATTACH 'database1.s3db' AS DB1;
  ATTACH 'database2.s3db' AS DB2;
  ATTACH 'database3.s3db' AS DB3;
  
  SELECT 'DB1' AS source, count(AnswerR2) AS cnt FROM DB1.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA'
  UNION ALL
  SELECT 'DB2' AS source, count(AnswerR2) AS cnt FROM DB2.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA'
  UNION ALL
  SELECT 'DB3' AS source, count(AnswerR2) AS cnt FROM DB3.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA'
  UNION ALL
  SELECT 'TOTAL' AS source, SUM(cnt) FROM (
      SELECT count(AnswerR2) AS cnt FROM DB1.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA'
      UNION ALL
      SELECT count(AnswerR2) AS cnt FROM DB2.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA'
      UNION ALL
      SELECT count(AnswerR2) AS cnt FROM DB3.tblPool WHERE qid = '96fb11e1-87b0-4cae-983b-9b9d849acbab' AND CorrectAns = 'A2' AND AnswerR2 != 'NA'
  );
  

注意：所有合并计数的场景必须用UNION ALL，不能用UNION。UNION会自带去重逻辑，如果两个库的计数值恰好相同，会被合并成一行，最终求和结果会出错。

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