如 : 上图,用上面的条件 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的那条。 不知现在了解么?麻烦了!
直接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
这个应该可以create view aa1 as 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这么写数据有重复,更新过去的值不是间隔时间最短的那条!
是更新data_log_2?更新什么字段?
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,更新两表时间间隔最短、偏差最小的
UPDATE data_log_2 a1 INNER JOIN (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`
or 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`
上图,用上面的条件
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`