CROSS/OUTER APPLY в MySQL

Question

Мне нужно использовать CROSS APPLY в MySQL (экземпляр MySQL EC2 RDS). Похоже, MySQL не распознает синтаксис CROSS APPLY. Может кто-то помочь мне, пожалуйста?

Вот запрос.

SELECT ORD.ID
    ,ORD.NAME
    ,ORD.DATE
    ,ORD_HIST.VALUE
FROM ORD
CROSS APPLY (
    SELECT TOP 1 ORD_HISTORY.VALUE
    FROM ORD_HISTORY
    WHERE ORD.ID = ORD_HISTORY.ID
        AND ORD.DATE <= ORD_HISTORY.DATE
    ORDER BY ORD_HISTORY.DATE DESC
    ) ORD_HIST

Answer 1

Ближайшим прямым приближением является соединение с коррелированным подзапросом в качестве предиката.

SELECT
   ORD.ID
  ,ORD.NAME
  ,ORD.DATE
  ,ORD_HISTORY.VALUE
FROM
  ORD
INNER JOIN
  ORD_HISTORY
    ON  ORD_HISTORY.<PRIMARY_KEY>
        =
        (SELECT ORD_HISTORY.<PRIMARY_KEY>
           FROM ORD_HISTORY
          WHERE ORD.ID = ORD_HISTORY.ID
            AND ORD.DATE <= ORD_HISTORY.DATE
       ORDER BY ORD_HISTORY.DATE DESC
          LIMIT 1
        )

В вашем случае, однако, вам нужно только одно поле из целевой таблицы. Это означает, что вы можете использовать коррелированный подзапрос непосредственно в операторе SELECT.

SELECT
   ORD.ID
  ,ORD.NAME
  ,ORD.DATE
  ,(SELECT ORD_HISTORY.VALUE
      FROM ORD_HISTORY
     WHERE ORD.ID = ORD_HISTORY.ID
       AND ORD.DATE <= ORD_HISTORY.DATE
  ORDER BY ORD_HISTORY.DATE DESC
     LIMIT 1
   )   AS VALUE
FROM
  ORD

Answer 2

Начиная с MySQL 8.0.14 вы можете использовать LATERAL:

Производной таблице теперь может предшествовать ключевое слово LATERAL, чтобы указать, что в одном и том же предложении FROM разрешено ссылаться (зависеть от) на столбцы предыдущих таблиц. Производная таблица, указанная с помощью LATERAL, может встречаться только в предложении FROM, либо в списке таблиц, разделенных запятыми, либо в спецификации соединения (JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN или RIGHT [OUTER] JOIN), Боковые производные таблицы делают возможными определенные операции SQL, которые не могут быть выполнены с сторонними производными таблицами или которые требуют менее эффективных обходных путей

CROSS APPLY () <=> ,LATERAL ()
OUTER APPLY () <=> LEFT JOIN LATERAL () ON 1=1

Добавлена ​​поддержка ЛАТЕРАЛЬНЫХ производных таблиц в MySQL 8.0.14

И в этом случае:

SELECT ORD.ID
    ,ORD.NAME
    ,ORD.DATE
    ,ORD_HIST.VALUE
FROM ORD,
LATERAL (
    SELECT ORD_HISTORY.VALUE
    FROM ORD_HISTORY
    WHERE ORD.ID = ORD_HISTORY.ID
        AND ORD.DATE <= ORD_HISTORY.DATE
    ORDER BY ORD_HISTORY.DATE DESC
    LIMIT 1
    ) ORD_HIST

Answer 3

На основе ответа @lujas szozda:

Для КРЕСТОВОГО ПРИМЕНЕНИЯ:

SELECT ORD.ID
    ,ORD.NAME
    ,ORD.DATE
    ,ORD_HIST.VALUE
FROM ORD JOIN 
LATERAL (
    SELECT ORD_HISTORY.VALUE
    FROM ORD_HISTORY
    WHERE ORD.ID = ORD_HISTORY.ID
        AND ORD.DATE <= ORD_HISTORY.DATE
    ORDER BY ORD_HISTORY.DATE DESC
    LIMIT 1
    ) ORD_HIST ON 1=1
/* ON 1=1 is just a trick to join to whatever the LATERAL returns */

Для ВНЕШНЕГО ПРИМЕНЕНИЯ:

SELECT ORD.ID
    ,ORD.NAME
    ,ORD.DATE
    ,ORD_HIST.VALUE
FROM ORD LEFT JOIN 
LATERAL (
    SELECT ORD_HISTORY.VALUE
    FROM ORD_HISTORY
    WHERE ORD.ID = ORD_HISTORY.ID
        AND ORD.DATE <= ORD_HISTORY.DATE
    ORDER BY ORD_HISTORY.DATE DESC
    LIMIT 1
    ) ORD_HIST ON 1=1 
/* ON 1=1 is just a trick to join to whatever the LATERAL returns */