如何比较日期时间并获取第一个和最后一个日期 MySQL

Question

我有下面提到的表格： MySQL 版本 - 5.7.25

表1：

ID             ref
T-1            abc-1-x
T-2            abc-2-x
T-3            abc-3-x
T-4            abc-4-x
T-5            abc-5-x

表2

ref_id        value          date
abc-1-x       250            2019-12-10 14:16:19
abc-2-x       500            2019-12-11 17:21:05
abc-3-x       100            2019-12-12 18:11:01
abc-4-x       150            2019-12-13 05:04:11
abc-5-x       120            2019-12-14 12:10:09

表3

ID           seq             date2
T-1          1               2019-12-10 14:05:04
T-1          2               2019-12-10 21:17:01
T-1          3               2019-12-11 13:12:12
T-2          1               2019-12-11 14:13:46
T-2          2               2019-12-12 14:05:04
T-3          1               2019-12-12 14:05:04
T-3          2               2019-12-12 17:15:14
T-3          3               2019-12-13 10:09:08
T-3          4               2019-12-14 12:18:14
T-4          1               2019-12-13 01:01:04
T-4          2               2019-12-13 03:03:01
T-5          1               2019-12-15 14:05:04

通过使用上述三个表，我想将表2与表3的date与每个ID进行比较，并根据每个id从表3中获取第一个日期和最后一个日期。

场景：

• 如果表3的任何日期小于表2中的特定id，则将其显示为Less
• 如果表3的任何日期等于表2中的特定id，则将其显示为Same
• 如果表3的任何日期大于表2中的特定id，则将其显示为Greater

所需输出：

ID          Value     date                  date1                 date2                remarks   day_diff
T-1         250       2019-12-10 14:16:19   2019-12-10 14:05:04   2019-12-11 13:12:12  Greater   1
T-2         500       2019-12-11 17:21:05   2019-12-11 14:13:46   2019-12-12 14:05:04  Greater   1
T-3         100       2019-12-12 18:11:01   2019-12-12 14:05:04   2019-12-14 12:18:14  Greater   2
T-4         150       2019-12-13 05:04:11   2019-12-13 01:01:04   2019-12-13 03:03:01  Same      0
T-5         120       2019-12-14 12:10:09   2019-12-15 14:05:04   Null                 Greater   1

Answer 1

您可以使用子查询为Table2中的每个条目生成Table3MAX和MIN日期，然后生成外部查询中的Greater/Same/Less值：

SELECT *, 
CASE WHEN DATE(date2) > DATE(date) OR DATE(date1) > DATE(date) THEN 'Greater'
WHEN DATE(date2) < DATE(date) OR DATE(date1) < DATE(date) THEN 'Less'
ELSE 'Same'
END AS remarks,
DATEDIFF(date2, date) AS day_diff
FROM (
SELECT t3.ID, t2.Value, t2.date, MIN(t3.date2) AS date1, MAX(t3.date2) AS date2
FROM Table1 t1
JOIN Table2 t2 ON t2.ref_id = t1.ref
JOIN Table3 t3 ON t3.ID = t1.ID
GROUP BY t3.ID, t2.Value, t2.date
) g

输出：

ID      Value   date                    date1                   date2                   remarks    day_diff
T-1     250     2019-12-10 14:16:19     2019-12-10 14:05:04     2019-12-11 13:12:12     Greater    1
T-2     500     2019-12-11 17:21:05     2019-12-11 14:13:46     2019-12-12 14:05:04     Greater    1
T-3     100     2019-12-12 18:11:01     2019-12-12 14:05:04     2019-12-14 12:18:14     Greater    2
T-4     150     2019-12-13 05:04:11     2019-12-13 01:01:04     2019-12-13 03:03:01     Same       0
T-5     120     2019-12-14 12:10:09     2019-12-15 14:05:04     2019-12-15 14:05:04     Greater    1

请注意，如果Table3中Table2值只有一个日期，这将为date1和date2给出相同的值。如果您确实希望在这种情况下NULLdate2，请将上述查询中的SELECT *更改为

SELECT ID, Value, date, date1,
CASE WHEN date1 = date2 THEN NULL ELSE date2 END AS date2,

在 dbfiddle 上演示