MySQL中的CROSS/OUTER APPLY

Question

我需要在MySQL（EC2 RDS MySQL实例）中使用CROSS APPLY语句。

看起来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

- hoz

---

那个 WHERE 子句看起来有问题。在 ORD.DATE 之前应该加上 AND 吗？ - Bob Jarvis - Слава Україні

你可能想看看这个链接：[http://forums.mysql.com/read.php?10,625014,625014#msg-625014]，或者是[https://dev59.com/qY_ea4cB1Zd3GeqPRa7f]。祝好运！ - Bob Jarvis - Слава Україні

非常抱歉，@BobJarvis，我已经修改了查询。 - hoz

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

- MatBailie

---

我尝试了第一种解决方案，但在 ORD 表的 7k 条记录中速度非常慢。真的很怀念 SQL SERVER。 - zhongxiao37

Answer 2

从MySQL 8.0.14开始，您可以使用LATERAL：

现在，派生表可以在前面加上LATERAL关键字，以指定它可以引用（依赖于）同一FROM子句中前面表的列。 使用LATERAL指定的派生表只能出现在FROM子句中，可以是用逗号分隔的表列表，也可以是连接规范（JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN或RIGHT [OUTER] JOIN）。Lateral派生表使得某些无法使用非lateral派生表完成的SQL操作成为可能，或者需要使用效率较低的变通方法。

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

MySQL 8.0.14新增了对LATERAL派生表的支持

而在这种情况下：

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演示

- Lukasz Szozda

---

你能解释一下 LEFT JOIN LATERAL () ON 1=1 是什么意思吗？这种语法在 MySql 8.0.18 中不起作用。 - Yván Ecarri

@YvánEcarri 当然，我添加了一个示例来说明这种情况。 - Lukasz Szozda

Answer 3

基于 @lujas szozda 的回答：

对于 CROSS APPLY：

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 */

对于 OUTER APPLY：

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 */

- Yván Ecarri