CROSS/OUTER APPLY in MySQL
MySQL lacks direct support for the CROSS APPLY syntax, but alternative approaches can be employed to achieve similar functionality.
Nested Sub-query:
In MySQL, a common alternative is to use a correlated sub-query as a predicate in a JOIN statement. This ensures that the sub-query's results are evaluated for each row in the outer query.
Example:
Given 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_HISTThe equivalent query using a nested sub-query would be:
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
)Direct Sub-query:
In cases where only a single field from the target table is required, a correlated sub-query can be directly integrated into 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
ORDThe above is the detailed content of How to Replicate CROSS/OUTER APPLY Functionality in MySQL?. For more information, please follow other related articles on the PHP Chinese website!
What does b-side and c-side mean?
bios cannot detect solid state drive
How to use python library
Rename the apk software
Solution to failed connection between wsus and Microsoft server
The main function of the arithmetic unit in a microcomputer is to perform
How to solve the problem when the computer CPU temperature is too high
java export excel
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
