表A
id user x1 x2 x3
1 张三 0 0 0
2 李四 0 0 0
3 王五 0 0 0表B
id Aid x1 x2 x3
1 1 1 5 2
2 1 5 6 12
3 1 11 2 8
4 2 8 8 10
5 2 9 1 6
6 3 5 5 7想 统计并更新 表A中不同用户在表B的x1,x2,x3的总量
id user x1 x2 x3
1 张三 0 0 0
2 李四 0 0 0
3 王五 0 0 0表B
id Aid x1 x2 x3
1 1 1 5 2
2 1 5 6 12
3 1 11 2 8
4 2 8 8 10
5 2 9 1 6
6 3 5 5 7想 统计并更新 表A中不同用户在表B的x1,x2,x3的总量
select aid,
sum(x1) as x1,
sum(x2) as x2,
sum(x3) as x3
from b
group by aid
) c on a.id=c.aid
set a.x1=c.x1,
a.x2=c.x2,
a.x3=c.x3
select aid,
sum(x1) as x1,
sum(x2) as x2,
sum(x3) as x3
from b
group by aid
) c on a.id=c.aid
set a.x1=c.x1,
a.x2=c.x2,
a.x3=c.x3
mysql> select * from a;
+------+------+------+------+------+
| id | user | x1 | x2 | x3 |
+------+------+------+------+------+
| 1 | 张三 | 0 | 0 | 0 |
| 2 | 李四 | 0 | 0 | 0 |
| 3 | 王五 | 0 | 0 | 0 |
+------+------+------+------+------+
3 rows in set (0.00 sec)mysql> select * from b;
+------+------+------+------+------+
| id | aid | x1 | x2 | x3 |
+------+------+------+------+------+
| 1 | 1 | 1 | 5 | 2 |
| 2 | 1 | 5 | 6 | 12 |
| 3 | 1 | 11 | 2 | 8 |
| 4 | 2 | 8 | 8 | 10 |
| 5 | 2 | 9 | 1 | 6 |
| 6 | 3 | 5 | 5 | 7 |
+------+------+------+------+------+
6 rows in set (0.00 sec)mysql> update a inner join (
-> select aid,
-> sum(x1) as x1,
-> sum(x2) as x2,
-> sum(x3) as x3
-> from b
-> group by aid
-> ) c on a.id=c.aid
-> set a.x1=c.x1,
-> a.x2=c.x2,
-> a.x3=c.x3;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from a;
+------+------+------+------+------+
| id | user | x1 | x2 | x3 |
+------+------+------+------+------+
| 1 | 张三 | 17 | 13 | 22 |
| 2 | 李四 | 17 | 9 | 16 |
| 3 | 王五 | 5 | 5 | 7 |
+------+------+------+------+------+
3 rows in set (0.00 sec)mysql>
ACMAIN_CHM