php - 如何减去类型不同的行的同一列？

Question

我想减去同一表中的行(where 子句不同)。

SELECT SUM(`balance`) AS `balance` FROM `vouchers`  WHERE `type`='CP' AND `post_status`='yes' GROUP BY `interactive_person`;

上面的查询为我提供了第一行的理想结果!第二个查询是

SELECT SUM(`balance`) AS `balance` FROM `vouchers`  WHERE `type`='CR' AND `post_status`='yes' GROUP BY `interactive_person`;

这些查询返回多于一行按交互人分组我希望它减去交互人(是一个数字)相同但类型改变的记录。简而言之，它就像 CashReceipt - CashPayment。我将第一个查询用作第二个查询的子查询，但错误显示子查询返回多于一行。我需要帮助来解决这个问题!

Answer 1

您可以使用 case 在同一查询中计算您的 sum()

SELECT 
SUM(CASE WHEN `type`='CP' THEN `balance` ELSE 0 END) AS CP_balance,
SUM(CASE WHEN `type`='CR' THEN `balance` ELSE 0 END) AS CR_balance,
SUM(CASE WHEN `type`='CP' THEN `balance` WHEN `type`='CR' THEN  -1 * `balance` ELSE 0 END) AS diff
FROM `vouchers`  
WHERE `post_status`='yes' 
GROUP BY `interactive_person`;