SQLite -对3个数据库的联合查询求和

Question

我在SQLite中有以下查询。

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

我得到的结果如下:

Table result
|1          |
|0          |
|0          |

得到和的最佳方法是什么?

Answer 1

您可以使用公共表表达式:

WITH counts(c) AS (
SELECT ... -- your original query here
)
SELECT sum(c) FROM counts;