在SQLite中，我想构建一个查询以针对“刻度”列表获取“标签”条目的最接近日期时间：

CREATE TABLE Tick    (
     id integer primary key,dt varchar(20) 
    );

INSERT INTO Tick (id,dt) VALUES 
( 1,'2018-10-30 13:00:00'),( 2,'2018-10-30 14:00:00'),( 3,'2018-10-30 15:00:00'),( 4,'2018-10-30 16:00:00'),( 5,'2018-10-30 17:00:00'),( 6,'2018-10-30 18:00:00'),( 7,'2018-10-30 19:00:00'),( 8,'2018-10-31 05:00:00'),( 9,'2018-10-31 06:00:00'),(10,'2018-10-31 07:00:00');


CREATE TABLE Tag    (
     id integer primary key,dt varchar(20) 
    );

INSERT INTO Tag (id,dt) VALUES 
(100,'2018-10-30 16:08:00'),(101,'2018-10-30 17:30:00'),(102,'2018-10-30 19:12:00'),(103,'2018-10-31 04:00:00'),(104,'2018-10-31 13:00:00');

以下查询为我提供了很好的匹配项（基于diff），但我无法获取Tick列：

SELECT Tag.dt,(SELECT ABS(strftime('%s',Tick.dt) - strftime('%s',Tag.dt)) as diff
        FROM Tick
        ORDER BY diff ASC
        LIMIT 1
       ) as diff from Tag

我尝试了以下操作，但是在ORDER BY中收到Tag.dt的错误消息：

SELECT 
     Tag.id,Tag.dt,Tick.id,Tick.dt,abs(strftime('%s',Tag.dt)) as Diff FROM Tag   JOIN Tick   ON Tick.dt =    (SELECT Tick.dt
    FROM Tick 
    ORDER BY abs(strftime('%s',Tag.dt)) ASC
    limit 1)

我想要的结果是这样的：

TagID,DateTimetag,TickID,DateTimeTick
    100,2018-10-30 16:08:00,4,2018-10-30 16:00:00
    101,2018-10-30 17:30:00,6,2018-10-30 18:00:00
    102,2018-10-30 19:12:00,7,2018-10-30 19:00:00
    103,2018-10-31 04:00:00,8,2018-10-31 05:00:00
    104,2018-10-31 13:00:00,10,2018-10-31 07:00:00

稍后编辑...
根据{{​​3}}的答案，我能够导出某些内容而无需使用FME中无法使用的ROW_COUNTER（）关键字。我还设置了最大增量时间差（10000秒）来找到匹配项：

SELECT t.TagId,t.Tagdt,t.TickId,t.Tickdt,MIN(t.Diff)
FROM
(
SELECT 
     Tag.id as TagId,Tag.dt as Tagdt,Tick.id as TickId,Tick.dt as Tickdt,Tag.dt)) as Diff
FROM Tag,Tick
WHERE Diff < 10000
) AS t
GROUP BY t.TagId

再次感谢！