join – Access(Jet)SQL：TableB中的DateTime标记位于TableA中每

第一句话

您可以安全地忽略以下部分(包括)JOIN：如果您只想破解代码,请启动Off.背景和结果只是作为背景.如果您想查看最初的代码,请查看2015-10-06之前的编辑历史记录.

目的

最后,我想基于表SecondTable中可用GPS数据的DateTime标记计算发射器(X或Xmit)的插值GPS坐标,该表位于表FirstTable中的观察点的正上方.

我实现最终目标的直接目标是弄清楚如何最好地将FirstTable加入SecondTable以获得那些侧翼时间点.后来我可以使用那些信息我可以计算中间GPS坐标,假设沿着等角坐标系进行线性拟合(花哨的话说我不关心地球是这个尺度的球体).

问题

>是否有更有效的方法来生成最接近的
之前和之后的时间戳？

>通过抓住我自己修复
“之后”,然后获得“之前”仅与之相关
“后”.

>是否存在不涉及(A> B OR A = B)结构的更直观的方式.

> Byrdzeye提供了基本的替代品,
然而,我的“现实世界”体验与他的所有4个人并不相符
加入执行相同的策略.但他完全赞不绝口
解决替代连接样式.

>您可能有任何其他想法,技巧和建议.

>因此,byrdzeye和Phrancis在这方面都非常有帮助.一世
发现Phrancis’ advice出色地布置和提供
在关键阶段提供帮助,所以我会在这里给他优势.

对于问题3,我仍然希望得到任何额外的帮助.
要点反映了我认为在个别问题上对我帮助最大的人.

表定义

半视觉表现

FirstTable

Fields
  RecTStamp | DateTime  --can contain milliseconds via VBA code (see Ref 1) 
  ReceivID  | LONG
  XmitID    | TEXT(25)
Keys and Indices
  PK_DT     | Primary,Unique,No Null,Compound
    XmitID    | ASC
    RecTStamp | ASC
    ReceivID  | ASC
  UK_DRX    | Unique,Compound
    RecTStamp | ASC
    ReceivID  | ASC
    XmitID    | ASC

SecondTable

Fields
  X_ID      | LONG AUTONUMBER -- seeded after main table has been created and already sorted on the primary key
  XTStamp   | DateTime --will not contain partial seconds
  Latitude  | Double   --these are in decimal degrees,not degrees/minutes/seconds
  Longitude | Double   --this way straight decimal math can be performed
Keys and Indices
  PK_D      | Primary,Simple
    XTStamp   | ASC
  UIDX_ID   | Unique,Simple
    X_ID      | ASC

ReceiverDetails表

Fields
  ReceivID                      | LONG
  Receiver_Location_Description | TEXT -- NULL OK
  Beginning                     | DateTime --no partial seconds
  Ending                        | DateTime --no partial seconds
  Lat                           | DOUBLE
  Lon                           | DOUBLE
Keys and Indicies
  PK_RID  | Primary,Simple
    ReceivID | ASC

ValidXmitters表

Field (and primary key)
  XmitID    | TEXT(25) -- primary,unique,no null,simple

SQL小提琴……

…这样您就可以使用表定义和代码
这个问题适用于MSAccess,但正如Phrancis指出的那样,Access没有SQL小提琴样式.所以,您应该能够go here查看基于Phrancis’ answer的表定义和代码：
http://sqlfiddle.com/#!6/e9942/4 (external link)

加入：开始

我目前的“内心胆量”加入战略

首先创建一个带有列顺序的FirstTable_rekeyed和复合主键(RecTStamp,ReceivID,XmitID)所有索引/排序的ASC.我还分别在每列上创建了索引.然后像这样填充它.

INSERT INTO FirstTable_rekeyed (RecTStamp,XmitID)
  SELECT DISTINCT ROW RecTStamp,XmitID
  FROM FirstTable
  WHERE XmitID IN (SELECT XmitID from ValidXmitters)
  ORDER BY RecTStamp,XmitID;

上面的查询用153006条记录填充新表,并在10秒左右的时间内返回.

当使用TOP 1子查询方法将整个方法包装在“SELECT Count(*)FROM(…)”中时,以下内容在一秒或两秒内完成

SELECT 
    ReceiverRecord.RecTStamp,ReceiverRecord.ReceivID,ReceiverRecord.XmitID,(SELECT TOP 1 XmitGPS.X_ID FROM SecondTable as XmitGPS WHERE ReceiverRecord.RecTStamp < XmitGPS.XTStamp ORDER BY XmitGPS.X_ID) AS AfterXmit_ID
    FROM FirstTable_rekeyed AS ReceiverRecord
    -- INNER JOIN SecondTable AS XmitGPS ON (ReceiverRecord.RecTStamp < XmitGPS.XTStamp)
         GROUP BY RecTStamp,XmitID;
-- No separate join needed for the Top 1 method,but it would be required for the other methods. 
-- Additionally no restriction of the returned set is needed if I create the _rekeyed table.
-- May not need GROUP BY either. Could try ORDER BY.
-- The three AfterXmit_ID alternatives below take longer than 3 minutes to complete (or do not ever complete).
  -- FIRST(XmitGPS.X_ID)
  -- MIN(XmitGPS.X_ID)
  -- MIN(SWITCH(XmitGPS.XTStamp > ReceiverRecord.RecTStamp,XmitGPS.X_ID,Null))

以前的“内部胆量”JOIN查询

首先(快……但不够好)

SELECT 
  A.RecTStamp,A.ReceivID,A.XmitID,MAX(IIF(B.XTStamp<= A.RecTStamp,B.XTStamp,Null)) as BeforeXTStamp,MIN(IIF(B.XTStamp > A.RecTStamp,Null)) as AfterXTStamp
FROM FirstTable as A
INNER JOIN SecondTable as B ON 
  (A.RecTStamp<>B.XTStamp OR A.RecTStamp=B.XTStamp)
GROUP BY A.RecTStamp,A.XmitID
  -- alternative for BeforeXTStamp MAX(-(B.XTStamp<=A.RecTStamp)*B.XTStamp)
  -- alternatives for AfterXTStamp (see "Aside" note below)
  -- 1.0/(MAX(1.0/(-(B.XTStamp>A.RecTStamp)*B.XTStamp)))
  -- -1.0/(MIN(1.0/((B.XTStamp>A.RecTStamp)*B.XTStamp)))

第二(慢)

SELECT
  A.RecTStamp,AbyB1.XTStamp AS BeforeXTStamp,AbyB2.XTStamp AS AfterXTStamp
FROM (FirstTable AS A INNER JOIN 
  (select top 1 B1.XTStamp,A1.RecTStamp 
   from SecondTable as B1,FirstTable as A1
   where B1.XTStamp<=A1.RecTStamp
   order by B1.XTStamp DESC) AS AbyB1 --MAX (time points before)
ON A.RecTStamp = AbyB1.RecTStamp) INNER JOIN 
  (select top 1 B2.XTStamp,A2.RecTStamp 
   from SecondTable as B2,FirstTable as A2
   where B2.XTStamp>A2.RecTStamp
   order by B2.XTStamp ASC) AS AbyB2 --MIN (time points after)
ON A.RecTStamp = AbyB2.RecTStamp;

背景

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!