SELECT A.lineid,A.equipmentid,A.mouldid,A.ability, ABS(A.ability-B.AVGability)/SUMNUM AS XS FROM TABLENAME A INNER JOIN (SELECT equipmentid,mouldid,AVG(ability) AS AVGability, SUM(ability) AS SUMNUM FROM TABLENAME GROUP BY equipmentid,mouldid ) B ON A.equipmentid=B.equipmentid AND A.mouldid=B.mouldid
分步,最好用存储过程:select equipmentid,mouldid,avg(ability) as avgability into #avgabilit from sc group by equipmentid,mouldidselect a.equipmentid,a.mouldid,sum(a.ability-b.avgability)/count(a.lineid) as xsc into #xscc from sc as a,#avgabilit t where a.equipmentid=t.equipmentid and a.mouldid=t.mouldid group by equipmentid,mouldidselect a.*,t.xsc*t1.avgability as xs from sc as a,#avgabilit t1,#xscc t where a.equipmentid=t.equipmentid and a.mouldid=t.mouldid and a.equipmentid=t1.equipmentid and a.mouldid=t1.mouldid
分步,最好用存储过程:select equipmentid,mouldid,avg(ability) as avgability into #avgabilit from sc group by equipmentid,mouldidselect a.equipmentid,a.mouldid,sum(a.ability-b.avgability)/count(a.lineid) as xsc into #xscc from sc as a,#avgabilit t where a.equipmentid=t.equipmentid and a.mouldid=t.mouldid group by equipmentid,mouldidselect a.*,t.xsc*t1.avgability as xs from sc as a,#avgabilit t1,#xscc t where a.equipmentid=t.equipmentid and a.mouldid=t.mouldid and a.equipmentid=t1.equipmentid and a.mouldid=t1.mouldid
declare @count1 float declare @sum1 float declare @average float select @sum1=sum(ability) from counttmp select @count1=count(ability) from counttmp select @average=@sum1/@count1 select lineid,equipmentid,mouldid,ability,abs(ability-@average)/@count1*@average as xs from counttmp group by lineid,equipmentid,mouldid,ability,ability 我测试过了,可以的话给我分哟
declare @count1 float declare @sum1 float declare @average float select @sum1=sum(ability) from counttmp select @count1=count(ability) from counttmp select @average=@sum1/@count1 select lineid,equipmentid,mouldid,ability,abs(ability-@average)/@count1*@average as xs from counttmp group by lineid,equipmentid,mouldid,ability,ability 我测试过了,你试一下,对就给我分
先做一个函数获取平均数 create function f_average() returns int as begin return (select sum(ability)/count(lineid) from sc) end 再写sql语句 select lineid,equipmentid,mouldid,ability,(ability-f_average())/(count(lineid)*f_average()) as xs from sc也可以先将平均值写入一个变量 上面的代码调整一下,应该能用
ABS(A.ability-B.AVGability)/SUMNUM AS XS
FROM TABLENAME A INNER JOIN
(SELECT equipmentid,mouldid,AVG(ability) AS AVGability,
SUM(ability) AS SUMNUM
FROM TABLENAME
GROUP BY equipmentid,mouldid
) B ON A.equipmentid=B.equipmentid AND A.mouldid=B.mouldid
into #avgabilit
from sc group by equipmentid,mouldidselect a.equipmentid,a.mouldid,sum(a.ability-b.avgability)/count(a.lineid) as xsc
into #xscc
from sc as a,#avgabilit t
where a.equipmentid=t.equipmentid
and a.mouldid=t.mouldid
group by equipmentid,mouldidselect a.*,t.xsc*t1.avgability as xs
from sc as a,#avgabilit t1,#xscc t
where a.equipmentid=t.equipmentid
and a.mouldid=t.mouldid
and a.equipmentid=t1.equipmentid
and a.mouldid=t1.mouldid
into #avgabilit
from sc group by equipmentid,mouldidselect a.equipmentid,a.mouldid,sum(a.ability-b.avgability)/count(a.lineid) as xsc
into #xscc
from sc as a,#avgabilit t
where a.equipmentid=t.equipmentid
and a.mouldid=t.mouldid
group by equipmentid,mouldidselect a.*,t.xsc*t1.avgability as xs
from sc as a,#avgabilit t1,#xscc t
where a.equipmentid=t.equipmentid
and a.mouldid=t.mouldid
and a.equipmentid=t1.equipmentid
and a.mouldid=t1.mouldid
declare @sum1 float
declare @average float
select @sum1=sum(ability) from counttmp
select @count1=count(ability) from counttmp
select @average=@sum1/@count1
select lineid,equipmentid,mouldid,ability,abs(ability-@average)/@count1*@average as xs from counttmp group by lineid,equipmentid,mouldid,ability,ability
我测试过了,可以的话给我分哟
declare @sum1 float
declare @average float
select @sum1=sum(ability) from counttmp
select @count1=count(ability) from counttmp
select @average=@sum1/@count1
select lineid,equipmentid,mouldid,ability,abs(ability-@average)/@count1*@average as xs from counttmp group by lineid,equipmentid,mouldid,ability,ability
我测试过了,你试一下,对就给我分
create function f_average()
returns int
as
begin
return (select sum(ability)/count(lineid) from sc)
end
再写sql语句
select lineid,equipmentid,mouldid,ability,(ability-f_average())/(count(lineid)*f_average()) as xs from sc也可以先将平均值写入一个变量
上面的代码调整一下,应该能用
计算针对 每个工作中心(设备编号,模具编号)
xs=Σ[ABS(差)]/该中心个数据条数*平均能力(平均差系数)