xLdcmz2q

1CREATE TABLE IF NOT EXISTS `users` (
2  `id` int(6) unsigned NOT NULL,
3  `phone` int(9) unsigned NOT NULL,
4  `recharge_amount` int(3)  NOT NULL,
5  `balance_before_recharge` int(3) NOT NULL,
6  PRIMARY KEY (`id`,`phone`)
7) DEFAULT CHARSET=utf8;
8INSERT INTO `users` (`id`, `phone`, `recharge_amount`, `balance_before_recharge`) VALUES
9  ('1', '930000001', '5', '2'),
10  ('2', '930000001', '10', '3'),
11  ('3', '930000001', '11', '4'),
12  ('4', '930000002', '14', '7'),
13  ('5', '930000003', '13', '6');
14
15ALTER TABLE `users`
16ADD  `balance_spent` decimal(19,3) NULL;
17
18update users u join
19       (select users.*,
20              (select next.balance_before_recharge
21               from users next
22               where next.id > users.id
23               order by id asc
24               limit 1
25              ) as next_col
26        from users
27       ) uu
28       on uu.id = u.id
29    set u.balance_spent = ((u.recharge_amount + u.balance_before_recharge - uu.next_col) / (u.recharge_amount + u.balance_before_recharge));