一、前言
开发中碰到的需求，获取两个数据表中日期字段的差值，并且取得差值最小的那条数据。本篇文章主要讲述如果通过函数获取mysql的日期差值，实际编写时遇到的问题，并且分析需求，得出最终sql等。
二、表结构以及需求
1、表结构
（1）a表结构：
"CREATE temporary TABLE if not exists a(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL DEFAULT 0,
`level_key` tinyint(1) unsigned NOT NULL DEFAULT 0,
`log_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_log_time` (`log_time`)
) ENGINE = MEMORY";
（2）b表结构：
"CREATE temporary TABLE if not exists b(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pay_id` int(10) unsigned NOT NULL DEFAULT 0,
`receiver_id` int(10) unsigned NOT NULL DEFAULT 0,
`pay_date` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_receiver_id` (`receiver_id`),
KEY `idx_pay_date` (`pay_date`)
) ENGINE = MEMORY";
这里的a表和b表都是内存临时表，需求是获取a表的log_time和b表的pay_date日期的差，并且获取差值最小的数据。
2、获取两个日期字段的差值函数
mysql> select timestampdiff(SECOND,'2019-3-15 05:20:20','2019-3-14 01:01:01') as s;
+-------+
| s |
+-------+
| 70841 |
+-------+
我们选用的是timestampdiff()函数，这个函数可以求出来两个日期的年差值，月差值，秒差值等：
select timestampdiff(YEAR,'2019-3-15 05:20:20','2019-3-14 01:01:01') as y; //年差值
select timestampdiff(QUARTER,'2019-3-15 05:20:20','2019-3-14 01:01:01') as q； //季度差值
select timestampdiff(SECOND,'2019-3-15 05:20:20','2019-3-14 01:01:01') as s ； //s差值
由于我们这里的两个时间日期离得比较近，所以选择了获取秒级的差值。这里需要注意，如果参数是时间戳的话，是不能正常计算出差值的，返回是null，所以参数需要是标准的日期格式。
3、两个内存临时表子查询报错
ERROR 1137 (HY000): Can't reopen table
本来是打算用子查询实现的，但是子查询格式类似于：
select * from a right join (
select * from a join b on xxx
) as d
由于临时表有一个特征，就是在同一个query语句中，你只能查找一次临时表。所以不能在一个sql里面查询同一个临时表两次。
4、获取日期差值的绝对值
ABS(timestampdiff(SECOND,b.pay_date,a.log_time));
使用ABS函数获取差值的正负值，方面后续的比较。
5、where条件中不能直接使用聚合函数后的值
select ABS(timestampdiff(SECOND,b.pay_date,a.log_time)) as diff
form a join b on xxxx where diff >0;
这种形式是不行的，因为sql的执行顺序如下：
(1)from
(2) on
(3) join
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum....
(7)having
(8) select
(9) distinct
(10) order by
(11) limit
我们在select 中进行函数计算后的字段，是不能直接放在where中使用条件查询的，因为mysql会在聚合函数之前就执行where中的条件查询。不过天无绝人之路，having是可以使用聚合计算后的值的，where条件执行对行的过滤，having执行分组的过滤，我们把对应的条件放在having之后就可以了。
三、最终的sql及结果展示
OK，上面分析这么多，顶多算是开发前的准备，既然如此，那就放码过来。
1、原来的数据结构
[0]=>
array(7) {
["user_id"]=>
string(3) "150"
["log_time"]=>
string(19) "2019-01-09 22:50:03"
["pay_date"]=>
string(19) "2019-01-09 22:19:09"
["diff"]=>
string(4) "1854"
}
[1]=>
array(7) {
["user_id"]=>
string(3) "150"
["log_time"]=>
string(19) "2019-01-09 23:00:03"
["pay_date"]=>
string(19) "2019-01-09 22:19:09"
["diff"]=>
string(4) "2454"
}
[2]=>
array(7) {
["user_id"]=>
string(3) "150"
["log_time"]=>
string(19) "2019-01-09 20:00:03"
["pay_date"]=>
string(19) "2019-01-09 22:19:09"
["diff"]=>
string(4) "-xxx"
}
可以看到,user_id = 150的数据一共有三条，并且时间差值有正有负。我们的需求是每个用户获取一条数据，并且对应的时间差值为最小的（正数）
2、最终sql及解析
"select xx, min(timestampdiff(SECOND,b.pay_date,a.log_time)) as diff from {tableA} as a
join {tableB} as b on a.user_id = b.receiver_id and a.log_time > b.pay_date
group by a.user_id having diff > 0";
解析：
(1)通过min(timestampdiff())函数获取两个时间的最小差值
(2)根据业务需求，差值需要大于0，就是log_time > pay_date，使用having操作diff别名
(3)使用group by分组，同时也为了后面的having分组过滤
3、最终结果
经过sql过滤之后，获取数据：
[0]=>
array(7) {
["user_id"]=>
string(3) "150"
["log_time"]=>
string(19) "2019-01-09 22:50:03"
["pay_date"]=>
string(19) "2019-01-09 22:19:09"
["diff"]=>
string(4) "1854"
}
总结：
这个sql并不难，只是刚开始自己给想复杂了，总想着子查询啊，排序获取啊之类的，实际上我们只需要好好分析下需求，把需求细化一下，然后组装后的sql就是我们需要的sql了。从这个sql上博主也学到很多东西，还是很开心的，记录一下。
end
转载：https://blog.csdn.net/LJFPHP/article/details/101209105