select 工件,
(select avg(误差) from table where 工件=a.工件) as 平均误差,
(select avg(误差) from table where 工件=a.工件 and 误差>=0) as 正平均误差,
(select avg(误差) from table where 工件=a.工件 and 误差<=0) as 负平均误差
from table as a
(select avg(误差) from table where 工件=a.工件) as 平均误差,
(select avg(误差) from table where 工件=a.工件 and 误差>=0) as 正平均误差,
(select avg(误差) from table where 工件=a.工件 and 误差<=0) as 负平均误差
from table as a
select 工件,
avg(误差) as 平均误差,
avg(case when 误差>=0 then 误差 else 0) as 正误差,
avg(case when 误差<=0 then 误差 else 0) as 负误差
from table
group by 工件
select 工件,
(select avg(誤差) from t where 工件=a.工件) as 平均误差,
(select avg(誤差) from t where 工件=a.工件 and 誤差>=0) as 正平均误差,
(select avg(誤差) from t where 工件=a.工件 and 誤差<=0) as 负平均误差
from t as a
group by 工件
from
(select 工件,avg(误差) as 平均误差 from tablename group by 工件) a
inner join
(select 工件,avg(误差) as 正误差 from tablename where 误差>=0 group by 工件) b
on a.工件=b.工件
inner join
(select 工件,avg(误差) as 正误差 from tablename where 误差<0 group by 工件) c
on a.工件=c.工件
(select avg(误差) from tablename b where a.工件=b.工件 and b.误差>=0) as 正误差,
(select avg(误差) from tablename c where a.工件=c.工件 and c.误差<0) as 负误差
from tablename a group by 工件
avg(误差) as 平均误差,
(select avg(誤差) from t where 工件=a.工件 and 誤差>=0) as 正平均误差,
(select avg(誤差) from t where 工件=a.工件 and 誤差<=0) as 负平均误差
from table a
group by 工件
select 工件,
avg(误差) as 平均误差,
avg(case when 误差 > 0 then 误差 else NULL end) as 正误差,
avg(case when 误差 < 0 then 误差 else NULL end) as 负误差
from table
group by 工件
或者:
select 工件,
(select avg(誤差) from t where 工件 = a.工件) as 平均误差,
(select avg(誤差) from t where 工件 = a.工件 and 誤差 > 0 ) as 正平均误差,
(select avg(誤差) from t where 工件 = a.工件 and 誤差 < 0 ) as 负平均误差
from t as a
group by 工件我想,使用case速度快點吧。