MySql在表之间导入数据

问题

存在两个表pay_record和pay_stats，结构和数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE `pay_record` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `pay_id` int(10) DEFAULT NULL,
  `amount` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
+----+--------+--------+
| id | pay_id | amount |
+----+--------+--------+
|  1 |      1 |     30 |
|  2 |      1 |     50 |
|  3 |      2 |     20 |
|  4 |      3 |     40 |
+----+--------+--------+

CREATE TABLE `pay_stats` (
  `pay_id` int(10) NOT NULL DEFAULT '0',
  `total` int(10) DEFAULT NULL,
  PRIMARY KEY (`pay_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
+--------+-------+
| pay_id | total |
+--------+-------+
|      1 |     0 |
|      2 |     0 |
|      3 |     0 |
+--------+-------+

将total改为pay_record中按pay_id统计的amount之和。

方法

1.使用update对join的表进行操作，语句如下：

1
2
3
4
5
UPDATE pay_stats AS s JOIN 
  (SELECT pay_id, SUM(amount) AS s_amnt FROM pay_record GROUP BY pay_id) AS r 
  ON s.pay_id = r.pay_id 
  SET s.total = r.s_amnt 
  WHERE s.pay_id = r.pay_id;

2.使用replace语句在不同的表之间导入数据，语句如下：

1
2
REPLACE INTO pay_stats (pay_id, total) 
  (SELECT pay_id, SUM(amount) AS s_amnt FROM pay_record GROUP BY pay_id);