我试图找出每个配方的最大/最小/平均工艺长度/步骤数以及使用这些配方的晶圆数量。 该表很大,并且按时间单位记录,因此有很多重复的数据,例如食谱或recipe_step。 这是我的代码,请帮助我:
select distinct
recipe as recipe_id,
max(pl) as max_process_length,
min(pl) as min_process_length,
avg(pl) as avg_process_length,
max(steps) as max_number_of_steps,
min(steps) as min_number_of_steps,
avg(steps) as avg_number_of_steps,
count(wfr_id) as number_of_wafers
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/process_id.xls'
from 01m02train
group by recipe
having (
max(pl) = (
select max(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
)
) as `maxpl`
and min(pl) = (
select min(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
)
) as `minpl`
and avg(pl) = (
select avg(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
)
) as `avgpl`
and max(steps) = (
select max(rs)
from (
select distinct count(recipe_step) as rs
from 01m02train
group by (recipe,wfr_id)
)
) as `maxrs`
and min(steps) = (
select min(rs)
from (
select distinct count(recipe_step) as rs
from 01m02train
group by (recipe,wfr_id)
)
) as `minrs`
and avg(steps) = (
select avg(rs)
from (
select distinct count(recipe_step) as rs
from 01m02train
group by (recipe,wfr_id)
)
) as `avgrs`
);
在你拥有比较子句中,你放置了太多( (,这些不能解析子查询的表名别名
having ( max(pl)=( <---- this must removed
select max(pt) from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
) <---- this must removed
) as `maxpl`
and min(pl)=( <---- this must removed
select min(pt) from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
)<---- this must removed
) as `minpl` and
.....
....
您必须删除子查询的相应 (( 对,如以下示例所示
having ( max(pl)=
select max(pt) from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
) as `maxpl`
and min(pl)=
select min(pt) from ( <---- this must removed
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
( 作为minpl
主要问题:错误消息非常清楚,您需要为表达式中生成的派生表添加别名。另一方面,为条件添加别名没有意义。所以基本上是这样的:
max(pl) = (
select max(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
)
) as `maxpl`
这应该拼写:
max(pl) = (
select max(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
) as `maxpl`
)
其他问题(它们不会产生错误,但在我看来仍然值得注意(:
group by
子句中枚举的列周围不需要括号(这是多余的(having
子句中的条件也是如此(除非您混合and
和or
,否则您不会(select distinct
group by
没有意义(本质上两者都是聚合(
以下是查询的更新版本:
select
recipe as recipe_id,
max(pl) as max_process_length,
min(pl) as min_process_length,
avg(pl) as avg_process_length,
max(steps) as max_number_of_steps,
min(steps) as min_number_of_steps,
avg(steps) as avg_number_of_steps,
count(wfr_id) as number_of_wafers
into outfile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/process_id.xls'
from 01m02train
group by recipe
having
max(pl) = (
select max(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
) as `maxpl`
)
and min(pl) = (
select min(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
) as `minpl`
)
and avg(pl) = (
select avg(pt)
from (
select count(time) as pt
from 01m02train
group by (recipe,wfr_id)
) as `avgpl`
)
and max(steps) = (
select max(rs)
from (
select count(recipe_step) as rs
from 01m02train
group by (recipe,wfr_id)
) as `maxrs`
)
and min(steps) = (
select min(rs)
from (
select count(recipe_step) as rs
from 01m02train
group by (recipe,wfr_id)
) as `minrs`
)
and avg(steps) = (
select avg(rs)
from (
select count(recipe_step) as rs
from 01m02train
group by (recipe,wfr_id)
) as `avgrs`
)
;