mysql - 用总和更新表

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

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

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

请注意，在 View 中，我们使用基于 > 的 ≥ 顺序:

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 的 View ，然后连接 pettytransactions 和 pettybalance 在 pc_id 上)。主要问题是触发器主体为创建或更新的每一行执行一次，这将导致它们的效率极其低下。另一种方法是创建 stored procedure更新列，您可以在插入或更新后调用。在获取余额时，过程比 View 更高效，但也更脆弱，因为由程序员来更新余额，而不是让数据库处理它。使用 View 是更简洁的设计。

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来存储货币值。由于 float 的表示方式，以 10 为基数的精确数字(即没有重复的小数表示)并不总是与 float 精确。例如，存储时，0.01(以 10 为基数)将更接近 0.009999999776482582... 或 0.0100000000000000002081668...。这很像 1/3 在 3 进制中是“0.1”，但在 10 进制中是 0.333333....。您应该使用 Decimal 而不是 Float。类型:

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

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