正确使用SQLite SUM()函数：C# WPF项目多表求和问题

我正在开发一个基于SQLite的C# WPF项目，数据库包含三张表，结构如下：

Name表 #

id - INTEGER
name - TEXT

Table1表 #

nameID - INTEGER
amnt1 - INTEGER
amnt2 - REAL
amnt3 - INTEGER
amnt4 - INTEGER
amnt5 - INTEGER
amnt6 - INTEGER
entryDate - TEXT

Table2表 #

id - INTEGER
nameID - INTEGER
amnt1 - INTEGER
amnt2 - INTEGER
amnt3 - INTEGER
amnt4 - INTEGER
amnt5 - INTEGER
amnt6 - INTEGER
location - INTEGER
entryDate - TEXT

我的需求是：先分别对Table1和Table2中entryDate在'2018-05-01'至'2018-05-20'范围内的各金额列求和，再将两张表的对应求和结果再次汇总。

我之前尝试用JOIN关联两张表来计算，但结果不正确——因为直接关联会产生笛卡尔积，导致符合条件的记录被重复计算，最终求和结果偏大。

测试用建表及数据插入语句 #

CREATE TABLE `Name` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT );
insert into `Name` VALUES(1,'test1');
insert into `Name` VALUES(2,'test2');

CREATE TABLE "Table1" ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `nameID` INTEGER, `amnt1` INTEGER, `amnt2` INTEGER, `amnt3` INTEGER, `amnt4` INTEGER, `amnt5` INTEGER, `amnt6` INTEGER, `entryDate` TEXT );
INSERT INTO Table1 VALUES(NULL,1,1,1,1,1,1,1,'2018-04-01');
INSERT INTO Table1 VALUES(NULL,2,1,1,1,1,1,1,'2018-05-01');
INSERT INTO Table1 VALUES(NULL,1,1,1,1,1,1,1,'2018-05-06');

CREATE TABLE "Table2" ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `nameID` INTEGER, `amnt1` INTEGER, `amnt2` INTEGER, `amnt3` INTEGER, `amnt4` INTEGER, `amnt5` INTEGER, `amnt6` INTEGER, `location` INTEGER, `entryDate` TEXT );
INSERT INTO Table2 VALUES(NULL,1,1,1,1,1,1,1,'2018-04-02');
INSERT INTO Table2 VALUES(NULL,1,1,1,1,1,1,1,'2018-05-05');
INSERT INTO Table2 VALUES(NULL,2,1,1,1,1,1,1,'2018-05-06');

正确的解决方案 #

方案1：分别子查询求和后相加

这种方法先单独计算每张表的金额总和，再将两个结果集的对应列相加，完全避免了关联带来的重复计算问题：

SELECT
    t1_sum.amnt1 + t2_sum.amnt1 AS total_amnt1,
    t1_sum.amnt2 + t2_sum.amnt2 AS total_amnt2,
    t1_sum.amnt3 + t2_sum.amnt3 AS total_amnt3,
    t1_sum.amnt4 + t2_sum.amnt4 AS total_amnt4,
    t1_sum.amnt5 + t2_sum.amnt5 AS total_amnt5,
    t1_sum.amnt6 + t2_sum.amnt6 AS total_amnt6
FROM
    (SELECT
         SUM(amnt1) AS amnt1,
         SUM(amnt2) AS amnt2,
         SUM(amnt3) AS amnt3,
         SUM(amnt4) AS amnt4,
         SUM(amnt5) AS amnt5,
         SUM(amnt6) AS amnt6
     FROM Table1
     WHERE entryDate BETWEEN '2018-05-01' AND '2018-05-20') AS t1_sum,
    (SELECT
         SUM(amnt1) AS amnt1,
         SUM(amnt2) AS amnt2,
         SUM(amnt3) AS amnt3,
         SUM(amnt4) AS amnt4,
         SUM(amnt5) AS amnt5,
         SUM(amnt6) AS amnt6
     FROM Table2
     WHERE entryDate BETWEEN '2018-05-01' AND '2018-05-20') AS t2_sum;

方案2：合并记录后统一求和

用UNION ALL把两张表中符合条件的金额记录合并成一个结果集，再对合并后的记录求和，写法更简洁：

SELECT
    SUM(amnt1) AS total_amnt1,
    SUM(amnt2) AS total_amnt2,
    SUM(amnt3) AS total_amnt3,
    SUM(amnt4) AS total_amnt4,
    SUM(amnt5) AS total_amnt5,
    SUM(amnt6) AS total_amnt6
FROM
    (SELECT amnt1, amnt2, amnt3, amnt4, amnt5, amnt6
     FROM Table1
     WHERE entryDate BETWEEN '2018-05-01' AND '2018-05-20'
     UNION ALL
     SELECT amnt1, amnt2, amnt3, amnt4, amnt5, amnt6
     FROM Table2
     WHERE entryDate BETWEEN '2018-05-01' AND '2018-05-20') AS combined;

这两种方法都能得到正确的结果：针对测试数据，Table1符合条件的2条记录各金额列总和为2，Table2符合条件的2条记录各金额列总和为2，最终汇总结果各列都是4。

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