用一个表中某列的[和]来更新另一个表的某列 mysql 5.0下测试通过:UPDATE b1 set qty2=(SELECT SUM(qty) from a1 GROUP by c1 having c1=c2) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 将表b中的c2设置为primary key or uniquereplace into b select b.c2,sum(a.qty) from b left join a on b.c2=a.c1 group by b.c2;测试数据如下:mysql> create table a(c1 varchar(10) not null,qty decimal(10));Query OK, 0 rows affected (0.36 sec)mysql> insert into a values('a1',1),('a1',2),('a1',3),('b1',4),('b2',5);Query OK, 5 rows affected (0.13 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> select * from a;+----+------+| c1 | qty |+----+------+| a1 | 1 || a1 | 2 || a1 | 3 || b1 | 4 || b2 | 5 |+----+------+5 rows in set (0.02 sec)mysql> CREATE TABLE b (c2 varchar(10) NOT NULL,qty2 decimal(10,0) default NULL,PRIMARY KEY(c2));Query OK, 0 rows affected (0.11 sec)mysql> insert into b values ('a1',0),('b1',0);Query OK, 2 rows affected (0.02 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from b;+----+------+| c2 | qty2 |+----+------+| a1 | 0 || b1 | 0 |+----+------+2 rows in set (0.03 sec)mysql> select b.c2,sum(a.qty) from b left join a on b.c2=a.c1 group by b.c2;+----+------------+| c2 | sum(a.qty) |+----+------------+| a1 | 6 || b1 | 4 |+----+------------+2 rows in set (0.12 sec)mysql> replace into b -> select b.c2,sum(a.qty) from b left join a on b.c2=a.c1 group by b.c2;Query OK, 4 rows affected (0.03 sec)Records: 2 Duplicates: 2 Warnings: 0mysql> select * from b;+----+------+| c2 | qty2 |+----+------+| a1 | 6 || b1 | 4 |+----+------+2 rows in set (0.01 sec) 字符集问题 ACCESS与mysql间数据转换问题。 mysql 字符集怪事求助! 我的MYSQL有非常多的TIME_WAIT链接 MySQL WorkBench的安装问题 触发器在页面新增数据发生错误 连接MySQL出错 text型字段怎么判断是否为空? mysql语句中条件like优化问题 关于Mysql中left join优化的问题,求指点 问题急,求一条sql语句,实现这样的功能,高手们请进!有分贡上!。。。。。。。。。。。。。 mysql 如何获得昨天插入的记录?
select b.c2,sum(a.qty) from b left join a on b.c2=a.c1 group by b.c2;测试数据如下:
mysql> create table a(c1 varchar(10) not null,qty decimal(10));
Query OK, 0 rows affected (0.36 sec)mysql> insert into a values('a1',1),('a1',2),('a1',3),('b1',4),('b2',5);
Query OK, 5 rows affected (0.13 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> select * from a;
+----+------+
| c1 | qty |
+----+------+
| a1 | 1 |
| a1 | 2 |
| a1 | 3 |
| b1 | 4 |
| b2 | 5 |
+----+------+
5 rows in set (0.02 sec)mysql> CREATE TABLE b (c2 varchar(10) NOT NULL,qty2 decimal(10,0) default NULL,PRIMARY KEY(c2));
Query OK, 0 rows affected (0.11 sec)mysql> insert into b values ('a1',0),('b1',0);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from b;
+----+------+
| c2 | qty2 |
+----+------+
| a1 | 0 |
| b1 | 0 |
+----+------+
2 rows in set (0.03 sec)mysql> select b.c2,sum(a.qty) from b left join a on b.c2=a.c1 group by b.c2;
+----+------------+
| c2 | sum(a.qty) |
+----+------------+
| a1 | 6 |
| b1 | 4 |
+----+------------+
2 rows in set (0.12 sec)mysql> replace into b
-> select b.c2,sum(a.qty) from b left join a on b.c2=a.c1 group by b.c2;
Query OK, 4 rows affected (0.03 sec)
Records: 2 Duplicates: 2 Warnings: 0mysql> select * from b;
+----+------+
| c2 | qty2 |
+----+------+
| a1 | 6 |
| b1 | 4 |
+----+------+
2 rows in set (0.01 sec)