MySQL事务自动结算器计算查询

Question

我正在为彼此共享账单的人创建一个小型自动结算程序网络应用程序。

举个简单的例子，如果 Sam、Bill 和 Jim 都住在一起并共享共同的账单，例如电费、煤气费和宽带费用，他们应该能够将每笔交易输入网络应用程序，然后应用程序自动计算谁应该向谁支付什么。

有两个表与这个问题有关:'transactions' 和 'transactionusers'。

交易: ID、用户 ID、零售商、商品、日期

交易用户 ID、交易ID、金额、用户ID

当 Sam 为电费(90 美元)添加一条“交易”记录并将金额平均分配给室友时，3 条记录将添加到“交易用户”表中。

我希望能够编写一个 SQL 查询来计算所有用户之间所有交易的总和，然后取走反向交易(例如，如果 Sam 欠 Bill $50，Bill 欠 Sam $20，那么结算就是 Bill欠山姆 30 美元)

我当前的查询允许您总结每对用户之间的总交易值(value)，但不带走反向交易 - 非常感谢任何帮助:

SELECT transactionusers.userID AS debtor, SUM(amount) AS owes, transactions.userID AS toUser FROM transactionusers
INNER JOIN transactions ON transactions.ID = transactionusers.transactionID
WHERE transactions.userID <> transactionusers.userID
GROUP BY transactionusers.userID, transactions.userID

示例数据

假设 UserIDs 是 Sam (1), Bill (2), Jim (3)

交易:

1, 1, 'Electricity Co', 'Electricity Bill'
2, 2, 'Gas Co', 'Gas Bill'
3, 3, 'Broadband Co', 'Broadband Charge'

交易用户:

1, 1, 30, 1
2, 1, 30, 2
3, 1, 30, 3

4, 2, 20, 1
5, 2, 20, 2
6, 2, 20, 3

7, 3, 5, 1
8、3、5、2
9, 3, 5, 3

示例记录中没有显示的是在用户之间不平等地分摊账单的能力；例如:如果吉姆在这所房子里多住一个月，他可能会承担更高部分的账单

Answer 1

你的查询很好，你只需要对同一个查询进行连接并扣除结果:

SELECT transactionusers.userID AS debtor, IF(SUM(amount)-r.owes < 0, 0, SUM(amount)-r.owes) AS owes, transactions.userID AS toUser FROM transactionusers
INNER JOIN transactions ON transactions.ID = transactionusers.transactionID
LEFT JOIN (
    SELECT transactionusers.userID AS debtor, SUM(amount) AS owes, transactions.userID AS toUser FROM transactionusers
    INNER JOIN transactions ON transactions.ID = transactionusers.transactionID
    WHERE transactions.userID <> transactionusers.userID
    GROUP BY transactionusers.userID, transactions.userID
) r ON r.debtor = transactions.userID AND r.toUser = transactionusers.userID
WHERE transactions.userID <> transactionusers.userID
GROUP BY transactionusers.userID, transactions.userID