mysql - mysql select 中的小计和总计不正确

Question

我已经设法在下面的 sql select 中分离出负数和正数，并且我正在尝试获取表图片中所示的小计和总计，第三列是正确的结果应该是什么。

下面是我使用过的sql select，但它返回上表第2列中的结果，并且不正确。如何获取第 3 列中的小计和总计结果

感谢和问候

SELECT * FROM
(SELECT
      party AS "Account",
      sum(debit-credit) AS "Balance"
 FROM `tabGL Entry`
 WHERE party_type ="Customer" AND MONTH(posting_date) = MONTH(Now()) -1
 GROUP BY party WITH ROLLUP
 HAVING sum(debit-credit) < 0
 UNION ALL
 SELECT
      party AS "Account",
      sum(debit-credit) AS "Balance"
 FROM `tabGL Entry`
 WHERE party_type ="Customer" AND MONTH(posting_date) = MONTH(Now()) -1
 GROUP BY party WITH ROLLUP
 HAVING sum(debit-credit) > 0
) rec

 UNION ALL

 SELECT NULL AS party, sum(dep) AS Balance 
 FROM (
 SELECT SUM(credit-debit) AS dep
 FROM `tabGL Entry`
 UNION ALL 
 SELECT SUM(credit-debit) AS nodep
 FROM `tabGL Entry`
) AS recb
 GROUP BY party WITH ROLLUP

Answer 1

使用此语法可以防止错误附加GROUP BY:

( SELECT ... GROUP BY ... )
UNION ALL
( SELECT ... GROUP BY ... )

对比

( SELECT ...  )
UNION ALL
( SELECT ...  )
GROUP BY ...

“小计”一词从何而来？我在查询中没有看到它。

我怀疑内部WITH ROLLUP被扔掉了。如果删除它们会发生什么？