if exists(select 1 from sysobjects where id=object_id('user20100618') and type='P')
Drop Procedure user20100618
go
Create procedure user20100618
as
Begin
declare @fbill_dat varchar(30),@tbill_dat varchar(30),@fcus_no varchar(30), @tcus_no varchar(30)set @fcus_no=(select fcus_no from SysDefine where display_name='毛利率' and host_id=host_id())
set @tcus_no =(select tcus_no from SysDefine where display_name='毛利率' and host_id=host_id())
set @Fbill_dat=(select fbill_dat from SysDefine where display_name='毛利率' and host_id=host_id())
set @Tbill_dat=(select tbill_dat from SysDefine where display_name='毛利率' and host_id=host_id())select
convert(varchar(30),null) as cus_no,
convert(varchar(50),null) as cus_nam,
convert(float,0) as p_quan,
convert(float,0) as nt_tot,
convert(float,0) as acost,
convert(float,0) as maot
into #User
from sob
where 1>2insert into #user(cus_no,cus_nam,p_quan,nt_tot,acost,maot)
select a.cus_no,c.cus_nam,b.p_quan,b.nt_tot,CONVERT(NUMERIC(20,2),(select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m ))*b.p_quan as acost, CONVERT(NUMERIC(20,4),((b.nt_tot-((select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m )*b.p_quan))/b.nt_tot))*(100) as maot
from so_recb b
left join so_reca a on b.out_no=a.out_no
left join CUS C ON c.cus_no=a.cus_no
where a.state='4'
and a.cus_no between @fcus_no and @tcus_no
and a.out_dat between @fbill_dat and @tbill_dat insert into #user(cus_no,cus_nam,p_quan,nt_tot,acost,maot)
select a.cus_no,c.cus_namn,b.p_quan*(-1),b.nt_tot*(-1),
CONVERT(NUMERIC(20,2), (select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m ))*b.p_quan*(-1) as acost,CONVERT(NUMERIC(20,4),((b.nt_tot-( (select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m )*b.p_quan))/b.nt_tot))*(-100) as maot
from so_srtb b
left join so_srta a on b.sr_no=a.sr_no
left join CUS C ON c.cus_no=a.cus_no
where a.state='1'
and a.cus_no between @fcus_no and @tcus_no
and a.sr_dat between @fbill_dat and @tbill_dat select f.cus_no,f.cus_nam,f.p_quan,f.nt_tot,f.acost,f.maot into #sum1 from
( select cus_no,cus_nam,SUm(isnull(p_quan,0)) as p_quan,
Sum(isnull(nt_tot,0)) as nt_tot,CONVERT(NUMERIC(20,2),sum(isnull(acost,0))) as acost,CONVERT(NUMERIC(20,3), (( Sum(isnull(nt_tot,0))-sum(isnull(acost,0)))/ Sum(isnull(nt_tot,0))*100)) as maot from #userDef group by cus_no,cus_nam)fselect sum(p_quan) as p_quan,sum(nt_tot) as nt_tot,sum(acost) as acost,(sum(nt_tot)-sum(acost))/sum(nt_tot)*100 as maot into #sum2 from #sum1
insert into #sum1( cus_no,cus_nam,p_quan,nt_tot,acost,maot) select '合計',''p_quan,nt_tot,acost,maot from #sum2select cus_no,cus_nam,p_quan,nt_tot,acost,maot from #sum1 drop table #user
drop table #sum1
drop table #sum2
End
go
exec user20100618
运行结果是
服务器: 消息 120,级别 15,状态 1,过程 user20100618,行 66
INSERT 语句的选择列表包含的项少于插入列表中的项。SELECT 语句中值的数目必须与 INSERT 语句中列的数目匹配。
服务器: 消息 2812,级别 16,状态 62,行 1
未能找到存储过程 'user20100618'。
Drop Procedure user20100618
go
Create procedure user20100618
as
Begin
declare @fbill_dat varchar(30),@tbill_dat varchar(30),@fcus_no varchar(30), @tcus_no varchar(30)set @fcus_no=(select fcus_no from SysDefine where display_name='毛利率' and host_id=host_id())
set @tcus_no =(select tcus_no from SysDefine where display_name='毛利率' and host_id=host_id())
set @Fbill_dat=(select fbill_dat from SysDefine where display_name='毛利率' and host_id=host_id())
set @Tbill_dat=(select tbill_dat from SysDefine where display_name='毛利率' and host_id=host_id())select
convert(varchar(30),null) as cus_no,
convert(varchar(50),null) as cus_nam,
convert(float,0) as p_quan,
convert(float,0) as nt_tot,
convert(float,0) as acost,
convert(float,0) as maot
into #User
from sob
where 1>2insert into #user(cus_no,cus_nam,p_quan,nt_tot,acost,maot)
select a.cus_no,c.cus_nam,b.p_quan,b.nt_tot,CONVERT(NUMERIC(20,2),(select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m ))*b.p_quan as acost, CONVERT(NUMERIC(20,4),((b.nt_tot-((select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m )*b.p_quan))/b.nt_tot))*(100) as maot
from so_recb b
left join so_reca a on b.out_no=a.out_no
left join CUS C ON c.cus_no=a.cus_no
where a.state='4'
and a.cus_no between @fcus_no and @tcus_no
and a.out_dat between @fbill_dat and @tbill_dat insert into #user(cus_no,cus_nam,p_quan,nt_tot,acost,maot)
select a.cus_no,c.cus_namn,b.p_quan*(-1),b.nt_tot*(-1),
CONVERT(NUMERIC(20,2), (select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m ))*b.p_quan*(-1) as acost,CONVERT(NUMERIC(20,4),((b.nt_tot-( (select isnull( m.end_price,0)
from (select top 1 inv_no, end_price ,max(cost_mon) as mon
from oinv_sum
where inv_no=b.inv_no
group by inv_no,end_price order by max(cost_mon) desc )m )*b.p_quan))/b.nt_tot))*(-100) as maot
from so_srtb b
left join so_srta a on b.sr_no=a.sr_no
left join CUS C ON c.cus_no=a.cus_no
where a.state='1'
and a.cus_no between @fcus_no and @tcus_no
and a.sr_dat between @fbill_dat and @tbill_dat select f.cus_no,f.cus_nam,f.p_quan,f.nt_tot,f.acost,f.maot into #sum1 from
( select cus_no,cus_nam,SUm(isnull(p_quan,0)) as p_quan,
Sum(isnull(nt_tot,0)) as nt_tot,CONVERT(NUMERIC(20,2),sum(isnull(acost,0))) as acost,CONVERT(NUMERIC(20,3), (( Sum(isnull(nt_tot,0))-sum(isnull(acost,0)))/ Sum(isnull(nt_tot,0))*100)) as maot from #userDef group by cus_no,cus_nam)fselect sum(p_quan) as p_quan,sum(nt_tot) as nt_tot,sum(acost) as acost,(sum(nt_tot)-sum(acost))/sum(nt_tot)*100 as maot into #sum2 from #sum1
insert into #sum1( cus_no,cus_nam,p_quan,nt_tot,acost,maot) select '合計',''p_quan,nt_tot,acost,maot from #sum2select cus_no,cus_nam,p_quan,nt_tot,acost,maot from #sum1 drop table #user
drop table #sum1
drop table #sum2
End
go
exec user20100618
运行结果是
服务器: 消息 120,级别 15,状态 1,过程 user20100618,行 66
INSERT 语句的选择列表包含的项少于插入列表中的项。SELECT 语句中值的数目必须与 INSERT 语句中列的数目匹配。
服务器: 消息 2812,级别 16,状态 62,行 1
未能找到存储过程 'user20100618'。
insert into #sum1( cus_no,cus_nam,p_quan,nt_tot,acost,maot)
select '合計',''p_quan,nt_tot,acost,maot from #sum2
insert into #sum1( cus_no,cus_nam,p_quan,nt_tot,acost,maot) select '合計','',p_quan,nt_tot,acost,maot from #sum2
'',p_quan