Set @sql= ' select carcode as '''+ '餐车编码'+''', carname as '''+ '餐车名称'+''' ,summoney as '''+ '应收总额'+''',hcmoney as '''+ '耗材金额'+''',realmoney as '''+ '销售金额'+''',cqts as '''+ '出勤天数'+''',rjxl as '''+ '日均销量'+''' 'exec(@sql+' from ZC_temp1_RJXSFXC' )我用PB动态调用的存储过程的显示值想这样显示: summoney 等字段想显示四位小数,请问如何格式化输出呀!
@ls_timebegin datetime ,
@ls_timeend datetime
AS
Declare @sql varchar(8000)IF Exists (Select Name From Sysobjects Where Name = 'ZC_temp1_RJXSFX' And Xtype ='U' )
Drop Table ZC_temp1_RJXSFXIF Exists (Select Name From Sysobjects Where Name = 'ZC_temp1_RJXSFXA' And Xtype ='U' )
Drop Table ZC_temp1_RJXSFXAIF Exists (Select Name From Sysobjects Where Name = 'ZC_temp1_RJXSFXB' And Xtype ='U' )
Drop Table ZC_temp1_RJXSFXBIF Exists (Select Name From Sysobjects Where Name = 'ZC_temp1_RJXSFXC' And Xtype ='U' )
Drop Table ZC_temp1_RJXSFXC--产生ZC_temp1_RJXSFX临时表数据
Select b.carcode,c.carname,sum(a.realnum*a.matchprice) as summoney,null as hcmoney,null as realmoney,null as cqts,null as rjxl
Into ZC_temp1_RJXSFX From T_MatchL_ZC a Join T_MatchM_ZC b On a.BillNo=b.BillNo Join T_DiningCar c On b.CarCode = c.CarCode
where b.BillDate Between @ls_timebegin and @ls_timeend and b.BillType = 201 group by b.carcode,c.carname--产生临时耗材金额
Select b.carcode,sum(a.realnum*a.matchprice) as hcmoney
Into ZC_temp1_RJXSFXA From T_MatchL_ZC a Join T_MatchM_ZC b On a.BillNo=b.BillNo Join T_Goods_ZC d On a.goodscode = d.goodscode
where b.BillDate Between @ls_timebegin and @ls_timeend and b.BillType = 201 and d.sortname = '耗材' group by b.carcode
--产生临时出勤天数
Select carcode,count(distinct billno) as cqts
Into ZC_temp1_RJXSFXB From T_MatchM_ZC
where BillDate Between @ls_timebegin and @ls_timeend and BillType = 201group by carcode--更新hcmoney,realmoney
Update a Set a.hcmoney = b.hcmoney,a.realmoney = a.summoney - b.hcmoney From ZC_temp1_RJXSFX as a,ZC_temp1_RJXSFXA as b where a.carcode = b.carcode
Update a Set a.cqts = b.cqts,a.rjxl = a.realmoney/b.cqts From ZC_temp1_RJXSFX as a,ZC_temp1_RJXSFXB as b where a.carcode = b.carcodeSelect * into ZC_temp1_RJXSFXC From ZC_temp1_RJXSFXSet @sql= ' select carcode as '''+ '餐车编码'+''', carname as '''+ '餐车名称'+''' ,summoney as '''+ '应收总额'+''',hcmoney as '''+ '耗材金额'+''',realmoney as '''+ '销售金额'+''',cqts as '''+ '出勤天数'+''',rjxl as '''+ '日均销量'+''' 'exec(@sql+' from ZC_temp1_RJXSFXC' )
GO
2.字段*1.0
是上面的a.hcmoney = b.hcmoney更新丢失的!5楼的方法也不行呀!