交叉/外部在MySQL中的应用 [英] CROSS/OUTER APPLY in MySQL

问题描述

我需要在MySQL(EC2 RDS MySQL实例)中使用CROSS APPLY. 看起来MySQL无法识别CROSS APPLY语法.有人可以帮我吗?

I need to use CROSS APPLY in MySQL (EC2 RDS MySQL instance). Looks like MySQL doesn't recognise the CROSS APPLY Syntax. Can someone help me please?

这是查询.

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

推荐答案

您最接近的 直接 近似是一个以相关子查询作为谓词的联接.

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语句中使用相关子查询.

In your case, however, you only need one field from the target table. This means that you are able to use the correlated sub-query directly in the SELECT statement.

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

这篇关于交叉/外部在MySQL中的应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！