昨天有个mysql数据库移植需求:
把online_log中每一个类型为2的用户,日志类型为0,最后一次日志的时间,导入到cam_info中相应用户的logout_time字段。cam_info中存储的是用户的id,online_log中存储的是用户名,通过account建立连接关系。
account 表:
id name ...
1 wang_wang
2 liang_liang
cam_info 表:
account_id ... logout_time
1
2
online_log 表:
id username type usertype time ...
1 wang_wang 0 2 2009-10-22 10:17:34
2 liang_liang 1 2 2009-10-22 10:18:34
3 liang_liang 0 2 2009-10-22 10:45:34
4 wang_wang 1 2 2009-10-22 11:17:34
5 wang_wang 0 2 2009-10-22 14:17:34
6 liang_liang 1 2 2009-10-22 14:45:34
7 wang_wang 1 2 2009-10-22 15:17:34
这还是有些烦人的:
online_log和cam_info属于一对多关系,数据时从多到一。
同一用户某个类型的日志有多条,要取最后一条。
两个表之间没有直接关联。
要移植对象是online_log中每一个用户。
用一条sql写出来。
在update语句中有循环处理的。
平时不喜欢sql的我,在他人的一直要求下,思考了许久,写出这个令人厌烦的sql:
上面的sql在msyql数据引擎为MyISAM类型时,性能较差,并且cam_info.account_id不在tre结果集中的logout_time被设置为当前时间,做如下修改:
把online_log中每一个类型为2的用户,日志类型为0,最后一次日志的时间,导入到cam_info中相应用户的logout_time字段。cam_info中存储的是用户的id,online_log中存储的是用户名,通过account建立连接关系。
account 表:
id name ...
1 wang_wang
2 liang_liang
cam_info 表:
account_id ... logout_time
1
2
online_log 表:
id username type usertype time ...
1 wang_wang 0 2 2009-10-22 10:17:34
2 liang_liang 1 2 2009-10-22 10:18:34
3 liang_liang 0 2 2009-10-22 10:45:34
4 wang_wang 1 2 2009-10-22 11:17:34
5 wang_wang 0 2 2009-10-22 14:17:34
6 liang_liang 1 2 2009-10-22 14:45:34
7 wang_wang 1 2 2009-10-22 15:17:34
这还是有些烦人的:
online_log和cam_info属于一对多关系,数据时从多到一。
同一用户某个类型的日志有多条,要取最后一条。
两个表之间没有直接关联。
要移植对象是online_log中每一个用户。
用一条sql写出来。
在update语句中有循环处理的。
平时不喜欢sql的我,在他人的一直要求下,思考了许久,写出这个令人厌烦的sql:
update cam_info set logout_time = (select tre.time from (select ac.id, re.time from (select id,username,time from online_log where type = 0 and user_type = 2 order by id desc) re ,account ac where ac.name = re.username group by re.username order by re.id) tre where tre.id = cam_info.account_id);
上面的sql在msyql数据引擎为MyISAM类型时,性能较差,并且cam_info.account_id不在tre结果集中的logout_time被设置为当前时间,做如下修改:
update cam_info set logout_time = (select tre.time from (select ac.id, re.time from (select id,username,time from online_log where type = 0 and user_type = 2 order by id desc) re ,account ac where ac.name = re.username group by re.username order by re.id) tre where tre.id = cam_info.account_id) where cam_info.account_id in (select distinct account.id from account,online_log where account.name = online_log.username);