主页 / dba / 问题 / 56378
Accepted
Nuoji
Asked: 2014-01-09 08:22:41 +0800 CST

用 MySQL 中前几行中列的总和更新列

  • 772

我们有一张桌子，基本上：

id、金额、期末余额、account_id

我现在想重新计算最终余额值（因为可能存在损坏）

期末余额基本上是“具有相同account_id的先前条目的期末余额”+金额。

假设有很多条目，但我想一次为一个帐户（或几个帐户）执行此操作。

（所以，如果我们有损坏的表：

Id  Amount Balance Account
1     100       0     1
2     100     100     2
3     -40     -40     1
4     100      60     1

我希望生成的数据库表是：

Id  Amount Balance Account
1     100     100     1
2     100     100     2
3     -40      60     1
4     100     160     1

)

mysql update

4 个回答

  1. 让表调用nuoji并用上面的数据加载它

    USE test
DROP TABLE IF EXISTS nuoji;
CREATE TABLE nuoji
(
  id int not null auto_increment primary key,
  amount INT NOT NULL,
  balance INT NOT NULL,
  account INT NOT NULL,
  KEY (account)
) ENGINE=MyISAM;
INSERT INTO nuoji (amount,balance,account) VALUES
(100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
SELECT * FROM nuoji;

    让我们运行该代码并制作数据：

    mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS nuoji;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE nuoji
    -> (
    ->   id int not null auto_increment primary key,
    ->   amount INT NOT NULL,
    ->   balance INT NOT NULL,
    ->   account INT NOT NULL,
    ->   KEY (account)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO nuoji (amount,balance,account) VALUES
    -> (100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |     -40 |       1 |
|  4 |    100 |      60 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql>

    这是您需要的查询

    SET @rt=0; SET @ndx=0; SET @given_account=1;
UPDATE nuoji A INNER JOIN  (SELECT * FROM
(SELECT (@ndx:=@ndx+1) ndx,id,amount,
(@rt:=@rt+amount) newbalance FROM nuoji
WHERE account=@given_account) BB) B USING (id)
SET A.balance=B.newbalance;

    这是执行的查询：

    mysql> SET @rt=0; SET @ndx=0; SET @given_account=1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE nuoji A INNER JOIN  (SELECT * FROM
    -> (SELECT (@ndx:=@ndx+1) ndx,id,amount,
    -> (@rt:=@rt+amount) newbalance FROM nuoji
    -> WHERE account=@given_account) BB) B USING (id)
    -> SET A.balance=B.newbalance;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |      60 |       1 |
|  4 |    100 |     160 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql>

    而已 ！！！

    如果其他账号也一样乱码，设置@given_account，然后执行代码。

    这是格式化的代码：

    SET @rt = 0;
SET @ndx = 0;
SET @given_account = 1;
UPDATE
    nuoji A INNER JOIN
    (
        SELECT * FROM
        (
            SELECT
                (@ndx:=@ndx+1) ndx,id,
                amount,(@rt:=@rt+amount) newbalance
            FROM nuoji
            WHERE account=@given_account
        ) BB
    ) B USING (id)
SET A.balance=B.newbalance;

    试试看 ！！！

    • 3 
  2. SET @a = 'z', @b = 'z';

UPDATE table AS q
    JOIN 
    (
        SELECT w.id
              ,w.amount
              ,w.balance
              ,w.account
              ,IF(@a = @a := w.account, @b := @b, @b := 0 ) AS foo
              ,IF(@b='z', @b := w.amount, @b := @b + w.amount) AS newbalance
            FROM table AS w
            ORDER BY w.account, w.id
    ) AS z ON q.id = z.id
    SET q.balance = z.newbalance;
    • 3

  3. 这是我的尝试。我这样做是为了让您可以一次更新表格，您不必单独指定帐户 ID。我还添加了更多示例数据。

    样本数据：

    CREATE TABLE t
    (`Id` int, `Amount` int, `Balance` int, `Account` int)
;

INSERT INTO t
    (`Id`, `Amount`, `Balance`, `Account`)
VALUES
    (1, 100, 0, 1),
    (2, 100, 100, 2),
    (3, -40, -40, 1),
    (4, 100, 60, 1),
    (5, 50, 25, 2),
    (6, 75, 30, 3)
;

    询问：

    update t inner join (
select
t.*
, @prevAmount := if(@prevAccount != Account, 0, @prevAmount)
, Amount + @prevAmount AS new_balance
, @prevAmount := Amount + @prevAmount
, @prevAccount := Account
from
t
, (select @prevAmount := 0, @prevAccount := (select min(account) from t)) v
order by Account, Id
) sq on t.Id = sq.Id
set t.Balance = sq.new_balance;

    结果：

    select * from t
order by Account, Id;

| ID | AMOUNT | BALANCE | ACCOUNT |
|----|--------|---------|---------|
|  1 |    100 |     100 |       1 |
|  3 |    -40 |      60 |       1 |
|  4 |    100 |     160 |       1 |
|  2 |    100 |     100 |       2 |
|  5 |     50 |     150 |       2 |
|  6 |     75 |      75 |       3 |
    • 2
  4. Best Answer

    一次修复一个帐户的解决方案：

    SET @b = 'z';
UPDATE table AS w
    SET w.balance = IF(@b='z', @b:=w.amount, @b:=@b+w.amount )
    WHERE w.account = 1
    ORDER BY w.id;
    • 1

相关问题