mysql获取两个表中日期字段的最小差值

一、前言

      开发中碰到的需求，获取两个数据表中日期字段的差值，并且取得差值最小的那条数据。本篇文章主要讲述如果通过函数获取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