主页 / dba / 问题 / 3415
Accepted
humble_coder
Asked: 2011-06-22 11:41:42 +0800 CST

是否可以编写一个自动协调/“交易”付款和费用的 SQL 查询

  • 772

我仍在处理此处提到的项目（http://dba.stackexchange.com/questions/2428/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system ）。

该系统将让用户选择以特定费用支付特定金额，或进行一般的“你想办法”付款。鉴于我们使用的表结构 (PAYMENTS, CHARGES, PAYMENTS_TO_CHARGES)，我想作弊。基本上，我正在寻找一个非常时髦的“协调”SQL 查询，它将执行以下操作：

第 1 步）用剩余余额（基本上是学分）获取所有付款

第 2 步）用剩余余额（部分支付等）获取所有费用

步骤 3) 在 PAYMENTS_TO_CHARGES 表中插入部分付款，直到没有更多可用信用或没有更多费用。

…我想从技术上讲，这与其说是创建交易数据不如说是对账，但你明白了。

步骤 1 和 2 显然非常简单。第 3 步才是杀手锏。如果在 SQL 中没有时髦的方法来做到这一点，我想我会使用旧的手动编码的逐步循环遍历每笔交易并发布 payment_to_charge ......只是想我会问。

提前致谢！

编辑1： 我提出了这个查询来确定哪些费用有剩余余额，但它给了我一个错误，说“'where子句'中的未知列'remaining_balance'”：

SELECT 
       charges.*
     , (charges.amount - transactions.total_paid) as remaining_balance 
FROM charges
   , (SELECT 
             charge_id
           , sum(amount) as total_paid 
      FROM payments_to_charges 
      GROUP BY charge_id) as transactions 
WHERE charges.member_id = 123 
  AND charges.id = transactions.charge_id 
  AND remaining_balance > 0 
  AND charges.active_on < NOW()

我确定某些项目只是乱序，但我无法弄清楚这个特定查询通常有什么问题。我应该使用 HAVING 而不是 WHERE 吗？我是否遗漏了其他完全明显的东西？

transaction query

1 个回答

  1. Best Answer

    您也许可以在一条 SQL 语句中将 INSERT 插入 Payemnts_To_Charges，但我不确定这是否值得。这似乎更容易在过程代码中构建、调试和维护。像这样的东西：

    CREATE TABLE Payments (PaymentId Number(10), Amount Number(6,2));
CREATE TABLE Charges  (ChargeID Number(10), Amount Number(6,2));
CREATE TABLE Payments_To_Charges 
   (PaymentID Number(10), ChargeID Number(10), Amount Number(6,2));

INSERT INTO Payments VALUES (1,4);
INSERT INTO Payments VALUES (2,4);

INSERT INTO Charges VALUES (1,2);
INSERT INTO Charges VALUES (2,5);
INSERT INTO Charges VALUES (3,6);
INSERT INTO Charges VALUES (4,4);
INSERT INTO Charges VALUES (5,10);

Declare
   vPaymentAmount Payments.Amount%Type;
   vAppliedAmount Payments_To_Charges.Amount%Type;
Begin
   For vPayment In (SELECT PaymentID, Amount FROM Payments) Loop
      vPaymentAmount := vPayment.Amount;      
      For vCharge In (
            SELECT ChargeID, Amount FROM
            (
               SELECT ChargeID, Amount - 
                  NVL((SELECT SUM(Amount) FROM Payments_To_Charges pc 
                       WHERE pc.ChargeID = c.ChargeID),0) Amount
               FROM Charges c            
            ) WHERE Amount > 0
         ) Loop
         vAppliedAmount := LEAST(vPaymentAmount, vCharge.Amount);
         INSERT INTO Payments_To_Charges (PaymentID, ChargeID, Amount)
            VALUES (vPayment.PaymentID, vCharge.ChargeID, vAppliedAmount);
         vPaymentAmount := vPaymentAmount - vAppliedAmount;
         If (vPaymentAmount = 0) Then
            Exit;
         End If;
      End Loop;
   End Loop;
End;
/

SELECT * FROM Payments_To_Charges;

    更新：

    剩余余额是问题。您不能在 WHERE 子句中引用别名值。您可以将查询设为子查询并将该条件添加到更高级别，或者更改 WHERE 子句中的剩余余额 (charges.amount - transactions.total_paid)。

    • 4

相关问题