代码之家 › 专栏 › 技术社区 › indago

用SUM更新表

sql-update mysql

indago · 技术社区 · 14 年前

我有一张叫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

但问题是,有人在昨天这样的前一天发布交易记录。上面的查询将只更新一行,而当前日期的其他行将具有错误的余额值。是否有一个查询或存储过程将更新整个表,以获得每个日期的正确余额?

1 回复 | 直到 14 年前

outis 14 年前

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)

注意,在视图中,我们使用基于以下项的≥顺序:

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) 表,并创建名为 矮星 比连接 小交易 和 pettybalance 在 pc_id ). 主要的问题是,触发器主体对创建或更新的每一行执行一次,这将导致它们的效率极低。另一种方法是创建 stored procedure 更新列,可以在插入或更新后调用。一个过程在获取平衡时比视图更高效,但在由程序员更新平衡而不是让数据库处理平衡时更脆弱。使用视图是更干净的设计。

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.00999999776482582。。。或0.0100000000000000002081668。。。储存时。这相当于基3中的1/3是“0.1”但0.333333。。。。以10为基数。而不是 浮子 ,您应该使用 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公司 ,也应该更改。