这个sql如何写
两个表如下
a (aid, user ,ip) b(bid, lasttime,ip ,aid)
aid 和bid都是int ,lasttime是time字段 , ip是varchar (ip地址)
a表的ip字段为空
现在要求根据a的aid从b表中取最后的一个lasttime时间的ip值 ,把这个ip值修改到a表的对应的ip字段。
(请注意aid在a表是主键, b表的aid是外键是大量重复,b表有200万记录)这个语句该如何写?
两个表如下
a (aid, user ,ip) b(bid, lasttime,ip ,aid)
aid 和bid都是int ,lasttime是time字段 , ip是varchar (ip地址)
a表的ip字段为空
现在要求根据a的aid从b表中取最后的一个lasttime时间的ip值 ,把这个ip值修改到a表的对应的ip字段。
(请注意aid在a表是主键, b表的aid是外键是大量重复,b表有200万记录)这个语句该如何写?
from a, b t
where a.id=t.aid
and not exists (select 1 from b where aid=a.id and lasttime>t.lasttime)
-----------------------------
crete temporary table t as select aid,ip from b
where ip =(select max(lasstime) from b group by aid where aid=b.aid);
update a set ip=t.ip where t..aid = a.aid;
可以,用如下方式:update a t1, b t2
set t1.ip=t2.ip
where t1.aid=t2.aid and not exists (select 1 from b t3 where t3.aid=t2.aid and t3.lasttime>t2.lasttime)
mysql> select * from a;
+------+------+------+
| aid | user | ip |
+------+------+------+
| 1 | kdjd | NULL |
| 2 | hgfk | NULL |
| 3 | rte | NULL |
| 4 | frd | NULL |
| 5 | igif | NULL |
+------+------+------+
5 rows in set (0.00 sec)mysql> select * from b;
+------+----------+--------------+------+
| bid | lasttime | ip | aid |
+------+----------+--------------+------+
| 1 | 12:13:56 | 192.168.0.12 | 1 |
| 2 | 05:14:56 | 192.168.3.12 | 1 |
| 3 | 14:56:10 | 192.168.10.2 | 1 |
| 4 | 19:13:42 | 192.168.0.12 | 2 |
| 5 | 20:30:00 | 192.168.0.23 | 2 |
| 6 | 21:23:10 | 192.168.0.22 | 3 |
| 7 | 22:00:00 | 192.168.0.54 | 4 |
| 8 | 23:12:00 | 192.168.1.1 | 3 |
+------+----------+--------------+------+
8 rows in set (0.00 sec)update a inner join(
select max(lasttime) mtime,ip,aid
from b
group by aid) b2
on a.aid=b2.aid
set a.ip=b2.ip;mysql> select * from a;
+------+------+--------------+
| aid | user | ip |
+------+------+--------------+
| 1 | kdjd | 192.168.0.12 |
| 2 | hgfk | 192.168.0.12 |
| 3 | rte | 192.168.0.22 |
| 4 | frd | 192.168.0.54 |
| 5 | igif | NULL |
+------+------+--------------+
5 rows in set (0.00 sec)
情况:
当同一aid下有多条记录时,就算你max(lasttime) 是对的,但ip值不一定是对应max(lasttime) 的那条记录的,而是同一aid下,排在最前面那条记录的ip值
我发现了,就是想不明白有group by 的时候除了集合列跟group by 的字段外,就不能有列了吗?为什么不取max()取到数据的那行呢!
像这种情况难道就只有一种写法吗?