mysql - SQL查询以检索最接近时间戳的记录

Question

我正在尝试从我的 MySQL 数据库中的表中检索记录，其中:

• 时间戳 最接近我提供的变量；并且，
• 按字段 keyA、keyB、keyC 和 keyD 分组

我已经按照下面的方式对变量进行了硬编码以对此进行测试，但是无法使查询正常工作。

SQLFiddle

我当前的架构是:

CREATE TABLE dataHistory (
          timestamp datetime NOT NULL,
          keyA varchar(10) NOT NULL,
          keyB varchar(10) NOT NULL,
          keyC varchar(25) NOT NULL,
          keyD varchar(10) NOT NULL,
          value int NOT NULL,
          PRIMARY KEY (timestamp,keyA,keyB,keyC,keyD)
          );

INSERT INTO dataHistory
    (timestamp, keyA, keyB, keyC, keyD, value)
VALUES
    ('2016-05-12 04:15:00', 'value1', 'all', 'value2', 'domestic', 96921),
    ('2016-05-12 04:05:00', 'value1', 'all', 'value2', 'domestic', 96947),
    ('2016-05-12 04:20:00', 'value1', 'all', 'value2', 'domestic', 96954),
    ('2016-05-12 04:15:00', 'value1', 'all', 'value3', 'domestic', 2732),
    ('2016-05-12 04:10:00', 'value1', 'all', 'value3', 'domestic', 2819),
    ('2016-05-12 04:20:00', 'value1', 'all', 'value3', 'domestic', 2802);

我目前的查询是:

SELECT e.difference, e.timestamp, e.keyA, e.keyB, e.keyC, e.keyD, e.value
FROM (SELECT TIMESTAMPDIFF(minute, '2016-05-12 04:11:00', d.timestamp) as difference, d.timestamp, d.keyA, d.keyB, d.keyC, d.keyD, d.value
    FROM dataHistory d
    GROUP BY d.keyA, d.keyB, d.keyC, d.keyD) as e;

我似乎可以从示例数据中提取的是最早的两条记录，而不是最接近日期时间的两条记录。我收到了什么:

difference  timestamp   keyA    keyB    keyC    keyD    value
-10 May, 12 2016 04:05:00   value1  all value2  domestic    96947
-5  May, 12 2016 04:10:00   value1  all value3  domestic    2819

我期待看到:

timestamp   keyA    keyB    keyC    keyD    value
May, 12 2016 04:15:00   value1  all value2  domestic    96921
May, 12 2016 04:10:00   value1  all value3  domestic    2819

如有任何帮助，我们将不胜感激!

Answer 1

SELECT e.difference, e.timestamp, e.keyA, e.keyB, e.keyC, e.keyD, e.value
FROM (SELECT ABS(TIMESTAMPDIFF(minute, '2016-05-12 04:11:00', d.timestamp)) as difference, d.timestamp, d.keyA, d.keyB, d.keyC, d.keyD, d.value
    FROM dataHistory d
    ORDER BY difference) as e
GROUP BY e.keyA, e.keyB, e.keyC, e.keyD;

此查询返回您想要的值。