mysql - 使用 CROSS JOIN 创建临时表

Question

我正在尝试为我拥有的应用程序数据创建一个临时表。当我运行子查询时，它工作正常，但是当我在临时表参数中运行它时，它会失败

错误代码:1054。“where 子句”中存在未知列“getApplicationsForRespID.app_id”

CREATE TEMPORARY TABLE CurrentApplications ENGINE=MEMORY AS 
(
SELECT *, planning_scheme.markus_ra, planning_scheme.metro_or_rural
FROM application
INNER JOIN key_table ON key_table.app_id = application.app_id
INNER JOIN planning_scheme ON planning_scheme.ps_code = application.planning_scheme

CROSS JOIN 
(
    SELECT key_table.app_id AS app_id2, planning_return.resp_authority AS resp_id
    FROM key_table
    INNER JOIN planning_return ON key_table.return_id = planning_return.return_id

)getApplicationsForRespID

WHERE application.app_id = getApplicationsForRespID.app_id
AND key_table.is_current = 1

)

Answer 1

在交叉联接子查询中，您有 key_table.app_id AS app_id2，这意味着不存在字段 getApplicationsForRespID.app_id 。

请在 WHERE 子句中使用 getApplicationsForRespID.app_id2!