select (CONVERT(char(10), a.OperateDt,120)) as OperateDt,a.WorkShopVch, a.FurnaceNoVch,sum(a.WeightDbl),a.TOGNameVch,a.MasterNameVch,
(select sum(WeightDbl) from YBrokenRecord where MasterCodeVch='21' and OperateDt=a.OperateDt) as A,
(select sum(WeightDbl) from YBrokenRecord where MasterCodeVch='23' and OperateDt=a.OperateDt) as b,
(select sum(WeightDbl) from YBrokenRecord where MasterCodeVch='26' and OperateDt=a.OperateDt) as c,
(select sum(WeightDbl) from YBrokenRecord where MasterCodeVch='09' and OperateDt=a.OperateDt) as d,
(select sum(WeightDbl) from YBrokenRecord where MasterCodeVch='12' and OperateDt=a.OperateDt) as e,
(select sum(WeightDbl) from YBrokenRecord where MasterCodeVch='16' and OperateDt=a.OperateDt) as f,
(select sum(WeightDbl) from YBrokenRecord where MasterCodeVch='01' and OperateDt=a.OperateDt) as g
from View_YBrokenRecord as a
inner join YBrokenRecord b on a.RecordIdVch=b.RecordIdVch
group by a.OperateDt,a.WorkShopVch, a.FurnaceNoVch,a.TOGNameVch,a.MasterNameVch order by OperateDt desc
莫非是这种的
select (select 1 from dual) * 100 / (select 5 from dual) || '%' as "百分比" from dual;
或者优化下:
select a.actual * 100 / t.total || '%' as "百分比"
from (select 1 actual from dual) a, (select 5 total from dual) t;
把除数用NULLIF(n,0)处理一下,除数真的为0时返回NULL,不会出错
select (CONVERT(char(10), a.OperateDt,120)) as OperateDt,a.WorkShopVch, a.FurnaceNoVch,sum(a.WeightDbl),a.TOGNameVch,a.MasterNameVch,
sum(case when MasterCodeVch='21' then WeightDbl else 0 end ) as A,
sum(case when MasterCodeVch='23' then WeightDbl else 0 end ) as b,
sum(case when MasterCodeVch='26' then WeightDbl else 0 end ) as c,
sum(case when MasterCodeVch='09' then WeightDbl else 0 end ) as d,
sum(case when MasterCodeVch='12' then WeightDbl else 0 end ) as e,
sum(case when MasterCodeVch='16' then WeightDbl else 0 end ) as f,
sum(case when MasterCodeVch='01' then WeightDbl else 0 end ) as g
from View_YBrokenRecord as a
inner join YBrokenRecord b on a.RecordIdVch=b.RecordIdVch
group by a.OperateDt,a.WorkShopVch, a.FurnaceNoVch,a.TOGNameVch,a.MasterNameVch order by OperateDt desc