MySql 如何避免创建临时表

Question

我对 SQL 非常陌生，需要通过 WordPress 插件 ( WP Data Tables ) 使用一个查询，但该插件不允许创建临时表。

要解决此问题，我相信我必须在联接中创建新表，但我不确定如何执行此操作。

我需要删除表创建的查询是:

create temporary table if not exists _SurveyData AS

(SELECT 
    _RefTable.RefID,
    _RefTable.ClientRef,
    _RefTable.SiteName,
    _JobTable.JobID,
    _JobTable.JobTypeID,
    _JobTable.JobClosedDate 
FROM
    _RefTable, _JobTable
WHERE
    _RefTable.RefID = _JobTable.RefID and  _JobTable.JobTypeID = 1);

create temporary table if not exists _InstallData AS

(SELECT 
    _RefTable.RefID,
    _RefTable.ClientRef,
    _RefTable.SiteName,
    _JobTable.JobID,
    _JobTable.JobTypeID,
    _JobTable.JobClosedDate 
FROM
    _RefTable, _JobTable
WHERE
    _RefTable.RefID = _JobTable.RefID and  _JobTable.JobTypeID = 2);

SELECT
    _RefTable.RefID,
    _RefTable.ClientRef,
    _RefTable.SiteName,
    _SurveyData.JobID,
    _SurveyData.JobClosedDate,
    _InstallData.JobID,
    _InstallData.JobClosedDate
from
    _RefTable, _SurveyData, _InstallData
Where
    _RefTable.RefID = _SurveyData.RefID and _RefTable.RefID = _InstallData.RefID
    order by RefID

任何帮助或正确方向的指出将不胜感激。

Answer 1

将临时表更改为子查询。

试试这个:

SELECT _RefTable.RefID,
    _RefTable.ClientRef,
    _RefTable.SiteName,
    _SurveyData.JobID,
    _SurveyData.JobClosedDate,
    _InstallData.JobID,
    _InstallData.JobClosedDate
from RefTable
INNER JOIN(
            SELECT 
            _RefTable.RefID,
            _RefTable.ClientRef,
            _RefTable.SiteName,
            _JobTable.JobID,
            _JobTable.JobTypeID,
            _JobTable.JobClosedDate 
                FROM RefTable, _JobTable WHERE _RefTable.RefID = _JobTable.RefID and  _JobTable.JobTypeID = 1
            ) as _SurveyData on _RefTable.RefID = _SurveyData.RefID 
INNER JOIN(
            SELECT 
            _RefTable.RefID,
            _RefTable.ClientRef,
            _RefTable.SiteName,
            _JobTable.JobID,
            _JobTable.JobTypeID,
            _JobTable.JobClosedDate 
            FROM  _RefTable, _JobTable WHERE _RefTable.RefID = _JobTable.RefID and  _JobTable.JobTypeID = 2
        ) as _InstallData on _RefTable.RefID = _InstallData.RefID
order by RefID;