sql - MS access 2016 中 OUTER APPLY 的等效命令

Question

我试图关联表以获取新信息，但在 MS 访问中似乎没有 OUTER APPLY。这是我的代码:

    SELECT *
FROM [pred-predi_calculo-presi]
  OUTER APPLY
    ( SELECT TOP 1 [snptc_histo].POSICIONBUS
      FROM [snptc_histo]
      WHERE [snptc_histo].PATENTEBUS = [pred-predi_calculo-presi].PATENTE
        AND [snptc_histo].horaConsulta < [pred-predi_calculo-presi].'pasadaReal'
      ORDER BY [snptc_histo].horaConsulta DESC
    ) AS p
  OUTER APPLY
    ( SELECT TOP 1 [snptc_histo].POSICIONBUS
      FROM [snptc_histo]
      WHERE [snptc_histo].PATENTEBUS = [pred-predi_calculo-presi].PATENTE
        AND [snptc_histo].horaConsulta >= [pred-predi_calculo-presi].'pasadaReal'
      ORDER BY [snptc_histo].horaConsulta ASC
    ) AS n ;

任何帮助都会受到欢迎

编辑:我原以为用 Java 代码(我心里有算法)做这个 Action 会更容易，而且更优化，但我没有合适的工具，比如数据结构或正确的“命令”与数据库一起工作抛出 java(我是数学家)所以要在 java 中执行此操作我需要更多帮助。尽管我愿意对此解决方案发表评论。这是一个示例数据:

pred-predi_calculo-presi:

Id1 ID                       Paradero'horaPrediccion''pasadaReal'PREDICCION_NUEVA   PATENTE servicio
1   2016-12-13 11:36:50_T518    PA52    11:36:51       11:36:20      1579,242       CJRW-73    518
2   2016-12-13 11:36:50_T518    PA52    11:36:51       11:36:20      761,211        BJFB-79    518
3   2016-12-13 12:11:02_T518    PC475   12:17:53       12:16:34      1382,545       BFKC-47    518
4   2016-12-13 12:11:02_T518    PC475   12:17:53       12:16:34      695,283        CJRW-74    518
5   2016-12-13 12:11:03_T418    PC206   12:16:27       12:15:50      1210,033       ZN-6742    418
6   2016-12-13 12:11:03_T418    PC206   12:16:27       12:15:50      1407,741       ZN-5814    418
7   2016-12-13 13:16:41_T516    PD208   13:17:56       13:18:02      842,634        BJFY-17    516

snptc_histo:

PATENTEBUS  POSICIONBUS horaConsulta
WA-9114     0           17:15:52
CJRW-83     3,64        17:15:52
ZN-3967     0,26        17:15:50
BJFV-89     11,06       17:15:51
BJFS-92     32,866      17:15:51
FLXJ-30     19,74       17:15:51
BJFR-52     9,55        17:15:50
CJRW-52     24,67       17:15:50
BJFR-94     14,75       17:15:49
CJRT-63     16,02       17:15:49

Answer 1

这不是 Access 中最简单的操作，但请尝试以下操作。

查询 1 - 为每个 PATENTEBUS 值查找小于 pasadaReal 的最大 horaConsulta 值的中间查询:

SELECT s.PATENTEBUS, MAX(horaConsulta) AS MaxHoraConsulta
FROM [snptc_histo] AS s 
INNER JOIN [pred-predi_calculo-presi] AS p ON s.PATENTEBUS = p.PATENTE
WHERE s.horaConsulta < p.pasadaReal
GROUP BY s.PATENTEBUS

Query2 - 使用 Query1 查找关联的 POSICIONBUS 值:

SELECT s.PATENTEBUS, s.POSICIONBUS
FROM [snptc_histo] AS s 
INNER JOIN Query1 AS q ON s.PATENTEBUS = q.PATENTEBUS AND s.horaConsulta = q.MaxHoraConsulta

现在创建类似的查询以查找大于或等于每个 PATENTEBUS 值的 pasadaReal 的最小 horaConsulta 值及其关联的 POSICIONBUS 值:

查询3

SELECT s.PATENTEBUS, MIN(horaConsulta) AS MinHoraConsulta
FROM [snptc_histo] AS s 
INNER JOIN [pred-predi_calculo-presi] AS p ON s.PATENTEBUS = p.PATENTE
WHERE s.horaConsulta >= p.pasadaReal
GROUP BY s.PATENTEBUS

查询4

SELECT s.PATENTEBUS, s.POSICIONBUS
FROM [snptc_histo] AS s 
INNER JOIN Query3 AS q ON s.PATENTEBUS = q.PATENTEBUS AND s.horaConsulta = q.MinHoraConsulta

现在您的最终查询变为:

SELECT p.*, q2.POSICIONBUS, q4.POSICIONBUS
FROM ([pred-predi_calculo-presi] AS p
LEFT JOIN Query2 AS q2 ON p.PATENTE = q2.PATENTEBUS)
LEFT JOIN Query4 AS q4 ON p.PATENTE = q4.PATENTEBUS