解决方案 »
- 打开远程连接出现的问题
- 关于 mysql 的 last_insert_id
- 连接MYSQL数据库出现乱码
- mysql-server和mysql-client和mysql-develop和mysql-share
- mysql在like中关键字设定的问题
- 如何在vc中动态的创建mysql数据库
- FREEBSD下对mysql表名大小写敏感,如何能使它对表名大小写不敏感?
- 帮我看看这样SQL语句,如何实现,等着要
- vs2010 打包程序如何将免安装版mysql放入安装包中?
- mysql数据导入的问题,csv
- mysql中root权限误删后如何恢复
- mysql怎样导出一个表里部分数据(按指定查询条件),导成sql脚本
上图,用上面的条件
left join data_log_2 b on a.phone_number=b.phone_number and a.user=b.user and TIMESTAMPDIFF(second,a.call_time,b.start_time) BETWEEN -10 and 20
就找到了两条数据 但下面这条 time_d=18的就比time_d=0的间隔时间长,这条是不符合的,我需要找并且更新的值 是time_d=0的那条。 不知现在了解么?麻烦了!
select
phone_number,bp,v_id,bv,call_time,start_time,min(time_d)
from (
SELECT a.`r_id`,a.phone_number,b.phone_number AS bp,a.v_id,b.v_id as bv,a.call_time,b.start_time,
TIMESTAMPDIFF(SECOND,a.call_time,b.start_time) AS time_d FROM data_log_1 a LEFT JOIN data_log_2 b
ON a.phone_number=b.phone_number AND a.user=b.user
AND TIMESTAMPDIFF(SECOND,a.call_time,b.start_time) BETWEEN -10 AND 20
)a1
group by phone_number,bp,v_id,bv,call_time,start_time
SELECT a.`r_id`,a.phone_number,b.phone_number AS bp,a.v_id,b.v_id as bv,a.call_time,b.start_time,
TIMESTAMPDIFF(SECOND,a.call_time,b.start_time) AS time_d FROM data_log_1 a LEFT JOIN data_log_2 b
ON a.phone_number=b.phone_number AND a.user=b.user
AND TIMESTAMPDIFF(SECOND,a.call_time,b.start_time) BETWEEN -10 AND 20;SELECT * FROM aa1 a WHERE NOT EXISTS(SELECT 1 FROM aa1 WHERE a.`phone_number`=`phone_number`
AND a.`v_id`=v_id AND a.`time_d`>`time_d`
)
如何写更新语句更新data_log_2呢?我最想实现的是update data_log_2 a,data_log_1 b set a.v_id=b.v_id
update data_log_2 a,data_log_1 b set a.v_id=b.v_id where
a.phone_number=b.phone_number and a.user=b.user and TIMESTAMPDIFF(second,b.call_time,a.start_time) BETWEEN -10 and 20这么写数据有重复,更新过去的值不是间隔时间最短的那条!
update data_log_2 a,data_log_1 b set a.v_id=b.v_id where
a.phone_number=b.phone_number and a.user=b.user and TIMESTAMPDIFF(second,b.call_time,a.start_time) BETWEEN -10 and 20把data_log_1.v_id更新到 data_log_2.v_id,更新两表时间间隔最短、偏差最小的
(SELECT * FROM aa1 a WHERE NOT EXISTS(SELECT 1 FROM aa1 WHERE a.`phone_number`=`phone_number`
AND a.`v_id`=v_id AND a.`time_d`>`time_d`
)) a
ON a.phone_number=a1.phone_number AND a.`r_id`=a1.`r_id`
SET a1.`v_id`=a.`v_id`
UPDATE data_log_2 a1 INNER JOIN
aa1 a
ON a.phone_number=a1.phone_number AND a.`r_id`=a1.`r_id`
SET a1.`v_id`=a.`v_id`