求一sql语句 本帖最后由 netxuning 于 2010-01-08 15:37:40 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 update a set num = select count(*) from b where rid=a.rid; update a inner join(select rid,count(*) as toa from b group by rid) bon a.rid=b.ridset a.num=b.toa update a set num = (select count(*) from b where rid=a.rid); 我记着mysql的update是不支持楼上这种写法的可以这么些update a ,(select count(*) num,rid from b group by rid) b set a.num=b.num WHERE b.rid=a.rid update a ,(select count(*) as cnt from b group by rid) xset a.num=x.cntwhere a.rid=b.rid 3楼的应该不行。where rid=a.rid出现了a.rid mysql> select * from a;+------+------+| rid | num |+------+------+| 1 | NULL || 2 | NULL || 3 | NULL |+------+------+3 rows in set (0.02 sec)mysql> select * from b;+------+------+| id | rid |+------+------+| 1 | 1 || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 3 || 6 | 3 |+------+------+6 rows in set (0.00 sec)mysql> update a set num=(select count(*) from b where rid=a.rid);Query OK, 3 rows affected (0.05 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from a;+------+------+| rid | num |+------+------+| 1 | 3 || 2 | 1 || 3 | 2 |+------+------+3 rows in set (0.00 sec)mysql> select version();+------------------+| version() |+------------------+| 5.1.36-community |+------------------+1 row in set (0.00 sec) 果然可以!zhoupuyue(.) 的方法可行。 一个简单的 mysql 触发器 mysql的float数据格式 【求助】强人请进 删除与自身表有外键约束关系的表中的数据 mysql如何取消一个权限 SQLYog不支持自动完成功能吗 linux下安装mysql的问题 困惑我一周 高手请近,关于数据暂存的问题 Mysql 5 的问题 江湖救急.........Mysql多服务器搭建问题,请个大神指导一下 在中文VB.NET换降下 日语学习软件的开发 乱码问题 错在那里啦~~触发器:两个数据库的表同步
(select rid,count(*) as toa from b group by rid) b
on a.rid=b.rid
set a.num=b.toa
update a ,(select count(*) num,rid from b group by rid) b set a.num=b.num WHERE b.rid=a.rid
set a.num=x.cnt
where a.rid=b.rid
mysql> select * from a;
+------+------+
| rid | num |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+------+------+
3 rows in set (0.02 sec)mysql> select * from b;
+------+------+
| id | rid |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
+------+------+
6 rows in set (0.00 sec)mysql> update a set num=(select count(*) from b where rid=a.rid);
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from a;
+------+------+
| rid | num |
+------+------+
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
+------+------+
3 rows in set (0.00 sec)mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.36-community |
+------------------+
1 row in set (0.00 sec)
zhoupuyue(.) 的方法可行。