用总和更新表

Question

我有一个名为 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;

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

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

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

Answer 1

触发器可能是您想要的。然而，让它正常有效地工作将是丑陋的。如果您要频繁地在较早的日期插入行，最好不要在每行中存储余额；相反，使用查询或视图来找到平衡。要查找特定日期的余额，请将其与较早日期的行连接起来，并对净存款求和，按当前交易 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 较小但日期相同的交易之后。更正式地，我们使用排序

一个> 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)

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

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.0100000000000000002081668...。这很像 1/3 在 3 进制中是“0.1”，但在 10 进制中是 0.333333....。您应该使用 Float。 mysql.com/doc/refman/5.1/en/ precision-math-decimal-changes.html" rel="nofollow">Decimal 类型：

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

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