如何获得 UNION ALL 的总和

Question

如何获取UNION ALL查询中所有行的总和。我正在尝试对每个 UNION 表的列求和，以及查询执行后的结果总和。$select = $db->prepare("SELECT SUM(money) AS cash FROM table1 WHERE money > 0 GROUP BY id                         UNION ALL                         SELECT SUM(payment) AS cash FROM table2 WHERE payment > 0 GROUP BY id                        UNION ALL                        SELECT SUM(pay) AS cash FROM table3 WHERE pay > 0 GROUP BY id                         ");        $select ->execute();        for($i=0;             $rows = $select ->fetch();             $i++){        $result = $rows['sum(cash)'];

Answer 1

仅使用 mysql 的可能解决方案如下，它返回表中所有三列总和的单个值：

SELECT SUM(pt.psum) AS tsum FROM 

(SELECT SUM(money) AS psum FROM table1 WHERE money > 0 GROUP BY id

UNION ALL

SELECT SUM(payment) AS psum FROM table2 WHERE money > 0 GROUP BY id

UNION ALL

SELECT SUM(pay) AS psum FROM table3 WHERE money > 0 GROUP BY id) pt

为清楚起见，

• psum：部分和

• pt：部分和表

• tsum：总和