时间戳

首页 > 解决方案 > 我需要过滤日期，只从最接近给定日期的重复中获取 1

mysql - 我需要过滤日期，只从最接近给定日期的重复中获取 1

问题描述

我正在尝试进行一个棘手的 SQL 查询，但它并不顺利。这个查询是关于 1 个表，它内部连接自身以删除基于starttimeand的任何重复项actualdatetime。因此，如果有重复，它必须查看哪个starttime日期最接近actualdatetime并只选择那个日期。

这是我的旧查询，它并没有完全查看actualdatetime所以我试图重写它：

SELECT *
FROM results t1
INNER JOIN (
   SELECT t2.numberprefix, MAX(t2.starttime) as starttime
   FROM results t2
   GROUP BY t2.numberprefix
) as t3 
ON t1.numberprefix=t3.numberprefix AND t1.starttime=t3.starttime
WHERE t1.numberprefix = '123' AND t1.portingid = '123'

我正在更新内部连接的子查询，但我有点迷失了这个：

SELECT t2.numberprefix, MAX(t2.starttime), 
MIN(t2.starttime = CASE t2.starttime
    WHEN DATEDIFF(t2.starttime, t2.actualdatetime) = 0 THEN 0
    WHEN DATEDIFF(t2.starttime, t2.actualdatetime) > 0 THEN DATEDIFF(t2.starttime, t2.actualdatetime)
    WHEN DATEDIFF(t2.starttime, t2.actualdatetime) < 0 THEN ABS(DATEDIFF(t2.starttime, t2.actualdatetime))
    END) 
FROM results t2 
GROUP BY t2.numberprefix

我的表格的简化版本results（我不知道如何在 StackOverflow 中制作表格）：

<table><tbody><tr><th>portingid</th><th>numberprefix</th><th>starttime</th><th>actualdatetime</th></tr><tr><td>123</td><td>111</td><td>2020-01-08 11:11:11</td><td>2020-01-02 11:11:11</td></tr><tr><td>123</td><td>111</td><td>2020-01-01 11:11:11</td><td>2020-01-02 11:11:11</td></tr><tr><td>123</td><td>555</td><td>2020-05-20 11:11:11</td><td>2020-05-04 11:15:11</td></tr><tr><td>123</td><td>555</td><td>2020-05-05 11:11:11</td><td>2020-05-04 11:15:11</td></tr><tr><td>123</td><td>555</td><td>2020-04-15 11:11:11</td><td>2020-05-04 11:15:11</td></tr></tbody></table>

所需的查询结果将是：

<table><tbody><tr><th>portingid</th><th>numberprefix</th><th>starttime</th><th>actualdatetime</th></tr><tr><td>123</td><td>111</td><td>2020-01-01 11:11:11</td><td>2020-01-02 11:11:11</td></tr><tr><td>123</td><td>555</td><td>2020-05-05 11:11:11</td><td>2020-05-04 11:15:11</td></tr></tbody></table>

标签： mysql

解决方案

通过使用timediff()和修复order by

SELECT t2.numberprefix, t2.starttime, 
FROM results t2      
WHERE t2.numberprefix = '+31636071280'
Order by timediff(t2.starttime, t2.actualdatetime) 
LIMIT 1

推荐阅读