MySQL中的交叉/外部应用

2022-01-23 00:00:00 join subquery mysql cross-apply

我需要在 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

推荐答案

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

Your closest direct approximation is a join with a correlated sub-query as the predicate.

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

相关文章