不好意思,上面的自己加的,原来的可执行的在下面
CREATE PROCEDURE ERP_seorderadd
AS
BEGIN
SET NOCOUNT ON
declare @s varchar(8000) ,
@sql varchar(8000)
set @s = 'select fnumber'
select @s = @s+ ',[ '+ a.fbillno + ']=sum(case when fbillno= ''' + a.fbillno + ''' then convert(decimal(9,0),foutqty) else 0 end) '
from (select distinct fbillno from erp_seorder)a
set @s = @s + ' from erp_seorder where foutqty>0
group by fnumber
'
set @sql='select x.物料编码,x.产品名称,x.出货数量,y.*,x. 新增订单数 from (select a.物料编码,b.fname 产品名称,a.出货数量,(a.出货数量-isnull(c.foutqty,0))新增订单数 from
(select 物料编码,数量 as 出货数量 from erp_seorderout where 单号=(select max(单号)单号 from erp_seorderout))a left join
t_icitem b on a.物料编码=b.fnumber left join
(select fnumber,sum(foutqty)foutqty from erp_seorder group by fnumber)c on b.fnumber=c.fnumber)x left join ('+ @s +')y on x.物料编码=y.fnumber order by x.物料编码 'exec(@sql)
SET NOCOUNT Off
end
GO
CREATE PROCEDURE ERP_seorderadd
AS
BEGIN
SET NOCOUNT ON
declare @s varchar(8000) ,
@sql varchar(8000)
set @s = 'select fnumber'
select @s = @s+ ',[ '+ a.fbillno + ']=sum(case when fbillno= ''' + a.fbillno + ''' then convert(decimal(9,0),foutqty) else 0 end) '
from (select distinct fbillno from erp_seorder)a
set @s = @s + ' from erp_seorder where foutqty>0
group by fnumber
'
set @sql='select x.物料编码,x.产品名称,x.出货数量,y.*,x. 新增订单数 from (select a.物料编码,b.fname 产品名称,a.出货数量,(a.出货数量-isnull(c.foutqty,0))新增订单数 from
(select 物料编码,数量 as 出货数量 from erp_seorderout where 单号=(select max(单号)单号 from erp_seorderout))a left join
t_icitem b on a.物料编码=b.fnumber left join
(select fnumber,sum(foutqty)foutqty from erp_seorder group by fnumber)c on b.fnumber=c.fnumber)x left join ('+ @s +')y on x.物料编码=y.fnumber order by x.物料编码 'exec(@sql)
SET NOCOUNT Off
end
GO
CREATE PROCEDURE ERP_seorderadd
AS
BEGIN
SET NOCOUNT ON
declare @s varchar(8000) ,
@sql varchar(8000)
set @s = 'select fnumber'
select @s = @s+ ',[ '+ a.fbillno + ']=sum(case when fbillno= ''' + a.fbillno + ''' then convert(decimal(9,0),foutqty) else 0 end) '
from (select distinct fbillno from erp_seorder)a
set @s = @s + ' from erp_seorder where foutqty> 0
group by fnumber
'
set @sql='select x.物料编码,x.产品名称,x.出货数量,y.*,x. 新增订单数 from (select a.物料编码,b.fname 产品名称,a.出货数量,(a.出货数量-isnull(c.foutqty,0))新增订单数 from
(select 物料编码,数量 as 出货数量 from erp_seorderout where 单号 like select (convert(varchar(10),max(convert(decimal(9,0),(substring(单号,6,len(单号)))))))单号
--------------改为下面试试:
单号 like (select ''%''+max(substring(单号,6,len(单号))) + ''%'' from erp_seorderout)a left join
t_icitem b on a.物料编码=b.fnumber left join
(select fnumber,sum(foutqty)foutqty from erp_seorder group by fnumber)c on b.fnumber=c.fnumber)x left join ('+ @s +')y on x.物料编码=y.fnumber order by x.物料编码 ' exec(@sql)
SET NOCOUNT Off
end
GO
AS
BEGIN
SET NOCOUNT ON
declare @s varchar(8000) ,
@sql varchar(8000)
set @s = 'select fnumber'
select @s = @s+ ',[ '+ a.fbillno + ']=sum(case when fbillno= ''' + a.fbillno + ''' then convert(decimal(9,0),foutqty) else 0 end) '
from (select distinct fbillno from erp_seorder)a
set @s = @s + ' from erp_seorder where foutqty> 0
group by fnumber
'
set @sql='select x.物料编码,x.产品名称,x.出货数量,y.*,x. 新增订单数 from (select a.物料编码,b.fname 产品名称,a.出货数量,(a.出货数量-isnull(c.foutqty,0))新增订单数 from
(select 物料编码,数量 as 出货数量 from erp_seorderout where 单号=(select max(单号)单号 from erp_seorderout))a left join
t_icitem b on a.物料编码=b.fnumber left join
(select fnumber,sum(foutqty)foutqty from erp_seorder group by fnumber)c on b.fnumber=c.fnumber)x left join '+ @s +' y on x.物料编码=y.fnumber order by x.物料编码 ' exec(@sql)
SET NOCOUNT Off
CREATE PROCEDURE ERP_seorderadd
AS
BEGIN
SET NOCOUNT ON
declare @s varchar(8000) ,
@sql varchar(8000)
set @s = 'select fnumber'
select @s = @s+ ',[ '+ a.fbillno + ']=sum(case when fbillno= ''' + a.fbillno + ''' then convert(decimal(9,0),foutqty) else 0 end) '
from (select distinct fbillno from erp_seorder)a
set @s = @s + ' from erp_seorder where foutqty> 0
group by fnumber
'
set @sql='select x.物料编码,x.产品名称,x.出货数量,y.*,x. 新增订单数 from (select a.物料编码,b.fname 产品名称,a.出货数量,(a.出货数量-isnull(c.foutqty,0))新增订单数 from
(select 物料编码,数量 as 出货数量 from erp_seorderout where 序号=(select max(序号)序号 from erp_seorderout))a left join
t_icitem b on a.物料编码=b.fnumber left join
(select fnumber,sum(foutqty)foutqty from erp_seorder group by fnumber)c on b.fnumber=c.fnumber)x left join ('+ @s +')y on x.物料编码=y.fnumber order by x.物料编码 ' exec(@sql)
SET NOCOUNT Off
end
GO
不知不加字段直接写在过程里应该是怎么样的