mysql - 在 select 语句和子查询中使用时表不存在错误

Question

即使定义了 t1，我也收到错误“表 'metrics.t1' 不存在”。我读过这里的几篇关于不存在错误的文章，但找不到解决方案。

如果我通过将整个 t1 查询插入到 from 语句中来替换“FROM t1 as t2”，我就可以使其工作。然而，这意味着巨大的 t1 查询会运行两次，大约需要 4 分钟。

SELECT 
  date_format(t1.date, '%Y') as year, date_format(t1.date, '%m') as month, date_format(t1.date, '%d') as day, t1.epc, t1.scrap, t1.freight, t1.smo, t1.extsort, t1.total, 
  ( SELECT SUM(t2.total) / COUNT(t2.total)
    FROM 
   t1 as t2
    WHERE DATEDIFF(t1.date, t2.date) BETWEEN 0 AND 92
  ) AS movavg
FROM (SELECT date, sum(epc_labor_cost) as epc, sum(scrap_value) as scrap, sum(prem_freight_cost) as freight, sum(smo_sort_hours) as smo, sum(extsort) as extsort, sum(epc_labor_cost+scrap_value+prem_freight_cost+smo_sort_hours+extsort) as total FROM (
                                           select pn, date, labor_cost as epc_labor_cost, 0 as scrap_value, 0 as prem_freight_cost, 0 as smo_sort_hours, 0 as extsort, 0 as total from epc_data 
                                 union all select pn, date, 0 as epc_labor_cost, abs(value) as scrap_value, 0 as prem_freight_cost, 0 as smo_sort_hours, 0 as extsort, 0 as total from mke_scrap 
                                 union all select pn, date, 0 as epc_labor_cost, 0 as scrap_value, cost as prem_freight_cost, 0 as smo_sort_hours, 0 as extsort, 0 as total from prem_freight 
                                 union all select pn, date, 0 as epc_labor_cost, 0 as scrap_value, 0 as prem_freight_cost, hours*4.02 as smo_sort_hours, 0 as extsort, 0 as total from smo_sort
                                union all select STRIP_NON_DIGIT(extsort.pn) as pn, date, 0 as epc_labor_cost, 0 as scrap_value, 0 as prem_freight_cost, 0 as smo_sort_hours, (extsort.sorted*pn_data.extsort_cost) as extsort, 0 as total from extsort inner join pn_data on STRIP_NON_DIGIT(extsort.pn)=STRIP_NON_DIGIT(pn_data.pn) ) as test group by year(date), week(date,3) ORDER BY date desc) AS t1
ORDER BY t1.date desc limit 26

我希望能够让它运行一次 t1 查询，以便在完整语句的两个部分中使用。

Answer 1

如果您想找到一种将子查询连接到自身的方法，您可能需要研究CTE(通用表表达式)。

不能在外部查询的 select 中从子查询中进行选择；您可以从子查询中选择字段。

确定

SELECT t.something AS aSomething
FROM (SELECT * FROM x) AS t
;

不行

SELECT (SELECT something FROM t LIMIT 1) AS aSomething
FROM (SELECT * FROM x) AS t
;

但是，使用 CTE，您可以执行以下操作:

WITH t AS (SELECT * FROM x)
SELECT t1.*, SUM(t2.something)
FROM t AS t1 
INNER JOIN t AS t2 ON t1.somevalue > t2.somevalue

我主要只在 MSSQL 中使用它们，所以我的 MySQL 语法可能有点偏差。