MySQL 中的 CROSS/OUTER APPLY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36869221/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CROSS/OUTER APPLY in MySQL
提问by hoz
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?
我需要在 MySQL（EC2 RDS MySQL 实例）中使用 CROSS APPLY。看起来 MySQL 无法识别 CROSS APPLY 语法。有人能帮助我吗？
Here's the query.
这是查询。
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
回答by MatBailie
Your closest directapproximation 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
)
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 语句中使用相关子查询。
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
回答by Lukasz Szozda
Starting from MySQL 8.0.14 you could use LATERAL:
从 MySQL 8.0.14 开始，您可以使用LATERAL：
A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause.A derived table specified with LATERAL can occur only in a FROM clause, either in a list of tables separated with commas or in a join specification (JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN, or RIGHT [OUTER] JOIN). Lateral derived tables make possible certain SQL operations that cannot be done with nonlateral derived tables or that require less-efficient workarounds
现在可以在派生表前面加上 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
Support for LATERAL derived tables added to MySQL 8.0.14
对添加到 MySQL 8.0.14 的 LATERAL 派生表的支持
And in this case:
在这种情况下：
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
