时间戳

首页 > 解决方案 > 在 select 语句和子查询中使用时，表不存在错误

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

问题描述

即使定义了 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 查询，以便在完整语句的两个部分中使用。

标签： mysql

解决方案

如果您试图找到一种将子查询加入自身的方法，您可能需要查看CTE（通用表表达式）。

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

好的

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 语法可能有点偏离。

推荐阅读