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

朴灿烈づ我的快乐病毒、 2023-10-11 12:27 18阅读 0赞

## 一、前言 ##

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

**总结：**

这个`sql`并不难，只是刚开始自己给想复杂了，总想着子查询啊，排序获取啊之类的，实际上我们只需要好好分析下需求，把需求细化一下，然后组装后的`sql`就是我们需要的`sql`了。从这个`sql`上博主也学到很多东西，还是很开心的，记录一下。

**end**