MySQL中的CROSS/OUTER APPLY

gopyfrb3  于 2023-10-15  发布在  Mysql
关注(0)|答案(3)|浏览(193)

我需要在MySQL中使用CROSS APPLY(EC2 RDS MySQL示例)。看起来像MySQL不承认交叉应用程序。有人能帮帮我吗
这里是查询。

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
xuo3flqw

xuo3flqw1#

最接近的 * 直接 * 近似是以相关子查询作为 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语句中使用相关子查询。

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
j7dteeu8

j7dteeu82#

从MySQL 8.0.14开始,你可以使用LATERAL

**派生表现在可以在LATERAL关键字之前,以指定允许引用(依赖)同一FROM子句中前面表的列。**用LATERAL指定的派生表只能出现在FROM子句中,或者出现在用逗号分隔的表列表中,或者出现在联接规范(JOIN,INNER JOIN,CROSS JOIN,LEFT [OUTER] JOIN,or RIGHT [OUTER] JOIN)中。横向派生表使某些SQL操作成为可能,而这些操作无法使用非横向派生表完成,或者需要效率较低的变通方法

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

Support for LATERAL derived tables added to 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;

如果相关子查询不返回任何行,则将跳过主查询中的主行。在这种情况下,应该使用LEFT JOIN LATERAL

db<>fiddle demo

9udxz4iz

9udxz4iz3#

基于@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 */

相关问题