如何获得 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 idUNION ALLSELECT SUM(payment) AS psum FROM table2 WHERE money > 0 GROUP BY idUNION ALLSELECT SUM(pay) AS psum FROM table3 WHERE money > 0 GROUP BY id) pt为清楚起见，psum：部分和pt：部分和表tsum：总和