如何比较日期时间并获得第一次和最后一次约会MySQL

Question

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

Table1：

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

Table2

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

Table3

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

通过使用上述三个表，我希望将date of Table2和Table3与每个ID进行比较，并根据每个id从Table3获取第一个日期和最后一个日期。

设想方案：

• 如果Table3的任何日期小于Table2中的特定id，则将其显示为Less
• 如果Table3的任何日期等于Table2中针对特定id的日期，则显示为Same
• 如果Table3的任何日期大于Table2中的特定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

您可以使用子查询从MAX和MIN日期为Table2中的每个条目从Table3生成日期，然后在外部查询中生成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

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

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

dbfiddle演示