用总和更新表 [英] UPDATE TABLE WITH SUM

问题描述

我有一张桌子叫做 pettycash

I have a table called pettycash

CREATE TABLE `pettycash` (
  `pc_id` int(7) NOT NULL AUTO_INCREMENT,
  `pc_date` date NOT NULL,
  `pc_in` double(13,2) DEFAULT '0.00',
  `pc_out` double(13,2) DEFAULT '0.00',
  `pc_bal` double(13,2) DEFAULT '0.00',
  `pc_ref` varchar(95) DEFAULT NULL,
  `pc_user` varchar(65) DEFAULT NULL,
  `pc_terminal` varchar(128) DEFAULT NULL,
  `pc_void` tinyint(1) DEFAULT '0',
   PRIMARY KEY (`pc_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

此表存储有关小额现金管理的数据，但我有一个简单的问题，即更新特定日期的余额.每次插入时，我都会运行以下查询:

This table stores data about the petty cash management,but i have a simple problem of updating the balance as at a particular date. Each time i insert i run the following query:

UPDATE pettycash a SET pc_bal=SUM(pc_in-pc_out) WHERE pc_id=" & newID 

但是当有人发布前一天(例如昨天)的交易时，问题就出现了.上述查询只会更新一行，而较当前日期的其他行将具有错误的余额值.是否有查询或存储过程可以更新整个表以获得每个日期的正确余额?

but the problem comes when someone comes to post transactions for a previous date like yesterday. the above query will only update one row and the other rows of a more current date will have wrong balance values. Is there a query or a Stored Procedure that will update the whole table getting the correct balance for each date?

推荐答案

触发器 可能是您想要的.然而，让它正常有效地工作将是丑陋的.如果您要频繁地在较早的日期插入行，最好不要将余额存储在每一行中；相反，使用查询或 views 来找到平衡.要查找特定日期的余额，请将其与较早日期的行连接，并按当前交易 ID 分组对净存款求和:

Triggers are probably want you want. However, getting this to work properly and efficiently will be ugly. It's probably better not to store the balance in each row if you're going to be inserting rows at earlier dates all that frequently; instead, use queries or views to find the balance. To find the balance on a particular date, join it with the rows for earlier dates and sum the net deposit, grouping by the current transaction ID:

CREATE VIEW pettybalance
  AS SELECT SUM(older.pc_in - older.pc_out) AS balance, 
            current.pc_id AS pc_id,  -- foreign key
            current.pc_date AS `date`
       FROM pettycash AS current
         JOIN pettycash AS older
           ON current.pc_date > older.pc_date 
              OR (current.pc_date = older.pc_date AND current.pc_id >= older.pc_id)
       GROUP BY current.pc_id
;

我还将 older.pc_id 限制为小于 current.pc_id 以修复与架构和余额计算相关的歧义.由于 pc_date 不是唯一的，您可以在给定日期进行多次交易.如果是这种情况，每笔交易的余额应该是多少?在这里，我们假设 ID 较大的事务发生在 ID 较小但日期相同的事务之后.更正式地，我们使用排序

I also restrict older.pc_id to be less than current.pc_id in order to fix an ambiguity relating to the schema and the balance calculation. Since the pc_date isn't unique, you could have multiple transactions for a given date. If that's the case, what should the balance be for each transaction? Here we assume that a transaction with a larger ID comes after a transaction with a smaller ID but that has the same date. More formally, we use the ordering

a > b ⇔a.pc_date > b.pc_date ∨(a.pc_date = b.pc_date ∧ a.pc_id > b.pc_id)

a > b ⇔ a.pc_date > b.pc_date ∨ (a.pc_date = b.pc_date ∧ a.pc_id > b.pc_id)

注意，在视图中，我们使用了 ≥基于 > 的顺序:

Note that in the view, we use a ≥ order based on >:

a ≥b ⇔a.pc_date > b.pc_date ∨(a.pc_date = b.pc_date ∧ a.pc_id ≥ b.pc_id)

a ≥ b ⇔ a.pc_date > b.pc_date ∨ (a.pc_date = b.pc_date ∧ a.pc_id ≥ b.pc_id)

在尝试让触发器正常工作后，我建议您甚至不要尝试.由于插入/更新时的内部表或行锁，您必须将 balance 列移动到新表，尽管这并不太麻烦(将 pettycash 重命名为 pettytransactions, 创建一个新的 pettybalance (balance, pc_id) 表，并创建一个名为 pettycash 的视图，然后连接 pettytransactions 和 pettybalance> 在 pc_id 上).主要问题是触发器主体为创建或更新的每一行执行一次，这将导致它们非常低效.另一种方法是创建一个 存储过程 来更新列，您可以在插入或更新后调用它们.在获取余额时，过程比视图更高效，但更脆弱，因为更新余额取决于程序员，而不是让数据库处理它.使用视图是更简洁的设计.

After trying to get triggers to work properly, I'm going to recommend not even trying. Due to internal table or row locks when inserting/updating, you have to move the balance column to a new table, though this isn't too onerous (rename pettycash to pettytransactions, create a new pettybalance (balance, pc_id) table, and create a view named pettycash than joins pettytransactions and pettybalance on pc_id). The main problem is that trigger bodies execute once for each row created or updated, which will cause them to be incredibly inefficient. An alternative would be to create a stored procedure to update columns, which you can call after inserting or updating. A procedure is more performant when getting balances than a view, but more brittle as it's up to programmers to update balances, rather than letting the database handle it. Using a view is the cleaner design.

DROP PROCEDURE IF EXISTS update_balance;
delimiter ;;
CREATE PROCEDURE update_balance (since DATETIME)
BEGIN
    DECLARE sincebal DECIMAL(10,2);
    SET sincebal = (
          SELECT pc_bal 
            FROM pettycash AS pc 
            WHERE pc.pc_date < since
            ORDER BY pc.pc_date DESC, pc.pc_id DESC LIMIT 1
        );
    IF ISNULL(sincebal) THEN
      SET sincebal=0.0;
    END IF;
    UPDATE pettycash AS pc
      SET pc_bal=(
        SELECT sincebal+SUM(net) 
          FROM (
            SELECT pc_id, pc_in - pc_out AS net, pc_date
              FROM pettycash
              WHERE since <= pc_date 
          ) AS older
          WHERE pc.pc_date > older.pc_date
             OR (pc.pc_date = older.pc_date 
                 AND pc.pc_id >= older.pc_id)
      ) WHERE pc.pc_date >= since;
END;;
delimiter ;

跑题

当前模式的一个问题是使用 Float 来存储货币值.由于浮点数的表示方式，以 10 为基数的精确数字(即没有重复的十进制表示)并不总是与浮点数精确.例如，存储时 0.01(以 10 为底)将更接近 0.009999999776482582... 或 0.01000000000000000002081668....这更像是基数 3 中的 1/3 是0.1"，但基数 10 中的 0.333333.....而不是 Float，您应该使用 Decimal 类型:

Off-topic

A problem with the current schema is the use of Floats to store monetary values. Due to how floating point numbers are represented, numbers that are exact in base 10 (i.e. don't have a repeating decimal representation) aren't always exact as floats. For example, 0.01 (in base 10) will be closer to 0.009999999776482582... or 0.0100000000000000002081668... when stored. It's rather like how 1/3 in base 3 is "0.1" but 0.333333.... in base 10. Instead of Float, you should use the Decimal type:

ALTER TABLE pettycash MODIFY pc_in DECIMAL(10,2);
ALTER TABLE pettycash MODIFY pc_out DECIMAL(10,2);

如果使用视图，删除 pettycash.pc_bal.如果使用存储过程来更新 pettycash.pc_bal，它也应该被改变.

If using a view, drop pettycash.pc_bal. If using a stored procedure to update pettycash.pc_bal, it too should be altered.

这篇关于用总和更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！