mysql - 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

通过利用上述三个表，我想将 Table2 的 date 与 Table3 与每个 ID 进行比较，并从 Table3 中获取第一个日期和最后一个日期每个 ID。

场景:

• 如果表 3 中的任何日期小于表 2 中的特定 ID，则将其显示为 Less
• 如果表 3 中的任何日期等于表 2 中的特定 ID，则将其显示为相同
• 如果表 3 中的任何日期大于表 2 中的特定 ID，则将其显示为更大

所需输出:

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

您可以使用子查询从 Table3 中为 Table2 中的每个条目生成 MAX 和 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 和 将为相同的值>日期2。在这种情况下，如果您确实希望 date2 为 NULL，请将上述查询中的 SELECT * 更改为

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

Demo on dbfiddle