mysql在update中使用select查询出的数据来更新数据，以及sql求两个日期时间相差的月数

记一个有意思的sql&＃xff0c;mysql在update中使用select查询出的数据来update&＃xff0c;我一开始是想先用sql把数据查询出来成一个列表&＃xff0c;然后再循环遍历update更新的&＃xff0c;但是效率太低了要执行很多遍sql&＃xff0c;为什么sql不能写成一条呢&＃xff0c;在update的时候直接select出来&＃xff0c;学习了一下&＃xff0c;发现可以。

update中使用select查询出的数据-通用模式&＃xff1a;
update a inner join (select * from b) c on a.id &＃61; c.id set a.xx &＃61; c.xxx

注&＃xff1a;在mysql中要想update中使用select查询出来的数据来更新的话只能用inner join

先描述一个场景&＃xff0c;我随便描述的&＃xff0c;场景不一定合理,我就想讲一下这种情况的解决方案&＃xff0c;给以后看到的小伙伴一点类似问题的解决思路。

&＃xff08;1&＃xff09;有一个读书使用表book_used_record&＃xff0c;&＃xff08;2&＃xff09;记录书本的每次使用记录&＃xff0c;&＃xff08;3&＃xff09;同一本书的使用记录的书编号book_no是一样的,&＃xff08;4&＃xff09;首次使用的时候before_book_no为null,&＃xff08;5&＃xff09;后面的使用记录都会记录上一次的before_book_no,&＃xff08;6&＃xff09;use_month记录这本书使用了多少个月

问题&＃xff1a;use_month记录的使用时长需要按同一本书最早的创建时间来记录&＃xff0c;即假如同一本书有三条记录&＃xff0c;这三条记录的use_month字段都是按这三条记录中创建时间最早的那一条创建时间来计算出来。
有一个定时任务&＃xff0c;每隔一段时间就需要更新use_month这个字段&＃xff0c;sql要如何写&＃xff1f;

book_used_record表的数据&＃xff1a;

查询出同一本书的最早创建时间不难&＃xff0c;这里主要由两个问题
1.如何查询出两个时间的相差月数
2.如何一条update的sql来更新&＃xff08;一条sql肯定得在update中用select查询出数据&＃xff09;

一、首先查询出两个时间的相差月数&＃xff1a;

TIMESTAMPDIFF(MONTH,create_time,NOW())

注&＃xff1a;

1. 一开始考虑用period_diff函数来处理的&＃xff0c;例&＃xff1a;select period_diff(201903,201901);查出来的结果就是2&＃xff0c;但是这里忽略了天数&＃xff0c;实际日期可能是201903.01&＃xff0c;201901.30,period_diff也是2&＃xff0c;相当于向上取整了&＃xff0c;但是我要向下取整&＃xff0c;不满两个月的还是按1个月来算。所以不合适。

2. 后面我想还可以通过datediff求出两个时间相差的天数然后取商就是向下取整&＃xff0c;但是算法用在日期上是不对的&＃xff0c;每个月不一定都是30天的&＃xff0c;所以也不合适。
   

3. period_diff需要date_format(curdate(),’%Y%m’)来格式化时间&＃xff0c;时间格式就是201901这样子的&＃xff0c;datediff函数的注意&＃xff0c;需要用date_format(curdate(),’%Y%m%d’)来格式化

4. DATE_FORMAT(now(), ‘%Y-%m-%d %H:%i:%S’)

5. 最后timestampdiff是最合适的
   –计算相差月数
   select TIMESTAMPDIFF(MONTH, ‘2018-03-20 23:59:00’, ‘2015-03-22 00:00:00’);
   –计算相差天数
   select TIMESTAMPDIFF(DAY, ‘2018-03-20 23:59:00’, ‘2015-03-22 00:00:00’);
   –计算相差小时数
   select TIMESTAMPDIFF(HOUR, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
   –计算相差秒数
   select TIMESTAMPDIFF(MINUTE, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);

二、在update中用select查询出的数据更新

UPDATE book_used_record book
INNER JOIN ( SELECT book_no, min( create_time ) as orignTime FROM book_used_record GROUP BY book_no) orign_book
ON book.book_no &＃61; orign_book.book_no
SET use_month &＃61; ( TIMESTAMPDIFF( MONTH, orign_book.orignTime, NOW( ) ) );


1. 先查询出书的编号&＃xff0c;以及该编号的最早的创建时间&＃xff0c;select book_no,min(create_time) FROM book_used_record
2. 连表更新&＃xff0c;set字段use_month 的时候最早创建时间用上面查询出的创建时间和当前时间来计算相差月数。update模式&＃xff1a;update a inner join (select * from b) c on a.id &＃61; b.id set a.xx &＃61; c.xxx
3. 更新之后可以看到use_month都正常统计了
   

相关sql&＃xff1a;

CREATE TABLE &＃96;book_used_record&＃96; (&＃96;id&＃96; int(11) NOT NULL COMMENT &＃39;主键&＃39;,&＃96;book_no&＃96; varchar(64) DEFAULT NULL COMMENT &＃39;书的编号&＃39;,&＃96;book_name&＃96; varchar(255) DEFAULT NULL COMMENT &＃39;书名&＃39;,&＃96;use_month&＃96; int(11) DEFAULT NULL COMMENT &＃39;使用时长&＃xff08;按月&＃xff09;&＃39;,&＃96;user_name&＃96; varchar(50) DEFAULT NULL COMMENT &＃39;使用人&＃39;,&＃96;user_code&＃96; varchar(64) DEFAULT NULL COMMENT &＃39;使用人编号&＃39;,&＃96;create_time&＃96; datetime DEFAULT NULL COMMENT &＃39;创建时间&＃39;,&＃96;before_user_code&＃96; varchar(64) DEFAULT NULL COMMENT &＃39;上一个使用人的编号&＃39;,PRIMARY KEY (&＃96;id&＃96;)
) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;utf8;INSERT INTO &＃96;test&＃96;.&＃96;book_used_record&＃96;(&＃96;id&＃96;, &＃96;book_no&＃96;, &＃96;book_name&＃96;, &＃96;use_month&＃96;, &＃96;user_name&＃96;, &＃96;user_code&＃96;, &＃96;create_time&＃96;, &＃96;before_user_code&＃96;) VALUES (1, &＃39;b1&＃39;, &＃39;孙子兵法&＃39;, NULL, &＃39;小明&＃39;, &＃39;g142&＃39;, &＃39;2019-07-01 23:08:35&＃39;, NULL);
INSERT INTO &＃96;test&＃96;.&＃96;book_used_record&＃96;(&＃96;id&＃96;, &＃96;book_no&＃96;, &＃96;book_name&＃96;, &＃96;use_month&＃96;, &＃96;user_name&＃96;, &＃96;user_code&＃96;, &＃96;create_time&＃96;, &＃96;before_user_code&＃96;) VALUES (2, &＃39;b1&＃39;, &＃39;孙子兵法&＃39;, NULL, &＃39;小红&＃39;, &＃39;g143&＃39;, &＃39;2019-09-01 23:09:28&＃39;, &＃39;g142&＃39;);
INSERT INTO &＃96;test&＃96;.&＃96;book_used_record&＃96;(&＃96;id&＃96;, &＃96;book_no&＃96;, &＃96;book_name&＃96;, &＃96;use_month&＃96;, &＃96;user_name&＃96;, &＃96;user_code&＃96;, &＃96;create_time&＃96;, &＃96;before_user_code&＃96;) VALUES (3, &＃39;b1&＃39;, &＃39;孙子兵法&＃39;, NULL, &＃39;小林&＃39;, &＃39;g144&＃39;, &＃39;2019-10-14 23:10:22&＃39;, &＃39;g143&＃39;);
INSERT INTO &＃96;test&＃96;.&＃96;book_used_record&＃96;(&＃96;id&＃96;, &＃96;book_no&＃96;, &＃96;book_name&＃96;, &＃96;use_month&＃96;, &＃96;user_name&＃96;, &＃96;user_code&＃96;, &＃96;create_time&＃96;, &＃96;before_user_code&＃96;) VALUES (4, &＃39;s2&＃39;, &＃39;sql优化&＃39;, NULL, &＃39;小威&＃39;, &＃39;g145&＃39;, &＃39;2019-08-01 23:11:14&＃39;, NULL);
INSERT INTO &＃96;test&＃96;.&＃96;book_used_record&＃96;(&＃96;id&＃96;, &＃96;book_no&＃96;, &＃96;book_name&＃96;, &＃96;use_month&＃96;, &＃96;user_name&＃96;, &＃96;user_code&＃96;, &＃96;create_time&＃96;, &＃96;before_user_code&＃96;) VALUES (5, &＃39;s2&＃39;, &＃39;sql优化&＃39;, NULL, &＃39;小华&＃39;, &＃39;g146&＃39;, &＃39;2019-11-14 23:11:44&＃39;, &＃39;g145&＃39;);
INSERT INTO &＃96;test&＃96;.&＃96;book_used_record&＃96;(&＃96;id&＃96;, &＃96;book_no&＃96;, &＃96;book_name&＃96;, &＃96;use_month&＃96;, &＃96;user_name&＃96;, &＃96;user_code&＃96;, &＃96;create_time&＃96;, &＃96;before_user_code&＃96;) VALUES (6, &＃39;k1&＃39;, &＃39;k8s的权威指南&＃39;, NULL, &＃39;小东&＃39;, &＃39;g147&＃39;, NULL, NULL);update book_used_record set use_month &＃61; null;
select *,TIMESTAMPDIFF(MONTH,create_time,now()) from book_used_record