1,除数不能为0 isnull(a.money,0)-isnull(c.money,0)/isnull(a.money,0)-isnull(b.money,0) as '利润率' from ( select deptname,prdname,carddt,expt as money from income a union select deptname,feename as prdname,carddt,outmoneyq as money from fee b union select deptname,prdname,carddt,expt as money from exptcost ) c---注意此处
--你三个表数据 union 后,就相当于只有一个表了,不可以用别名 a,b ,c去取得各分表的数据 --按照你的计算方法写出:select a.deptname, a.prdname, a.carddt, '销售净额'=isnull(a.[money],0)-isnull(b.[money],0), '利润'=isnull(a.[money],0)-isnull(c.[money],0), '利润率'=(isnull(a.[money],0)-isnull(c.[money],0))/(isnull(a.[money],0)-isnull(b.[money],0)) from income a left join fee b on a.deptname=b.deptname and a.prdname=b.prdname and a.carddt=b.carddt left join exptcost c on a.deptname=c.deptname and a.prdname=c.prdname and a.carddt=c.carddt group by a.deptname,a.prdname,a.carddt
isnull(a.money,0)-isnull(c.money,0)/isnull(a.money,0)-isnull(b.money,0) as '利润率' from
(
select deptname,prdname,carddt,expt as money from income a union
select deptname,feename as prdname,carddt,outmoneyq as money from fee b union
select deptname,prdname,carddt,expt as money from exptcost ) c---注意此处
--按照你的计算方法写出:select a.deptname,
a.prdname,
a.carddt,
'销售净额'=isnull(a.[money],0)-isnull(b.[money],0),
'利润'=isnull(a.[money],0)-isnull(c.[money],0),
'利润率'=(isnull(a.[money],0)-isnull(c.[money],0))/(isnull(a.[money],0)-isnull(b.[money],0))
from income a
left join fee b on a.deptname=b.deptname and a.prdname=b.prdname and a.carddt=b.carddt
left join exptcost c on a.deptname=c.deptname and a.prdname=c.prdname and a.carddt=c.carddt
group by a.deptname,a.prdname,a.carddt