use tempdb goCREATE TABLE BOM单(产品 varchar(10),材料 varchar(10),消耗 decimal(28,6)) declare @i int declare @J int set @J = 0 set @i = 0 while @I < 4 begin while @j < 26 begin insert bom单 select char(65+@i)+char(65+@j),N'a',2 insert bom单 select char(65+@i)+char(65+@j),N'b',2 insert bom单 select char(65+@i)+char(65+@j),N'c',2 insert bom单 select char(65+@i)+char(65+@j),N'd',2 insert bom单 select char(65+@i)+char(65+@j),N'e',2 insert bom单 select char(65+@i)+char(65+@j),N'f',2 insert bom单 select char(65+@i)+char(65+@j),N'g',2 insert bom单 select char(65+@i)+char(65+@j),N'h',2 insert bom单 select char(65+@i)+char(65+@j),N'i',2 insert bom单 select char(65+@i)+char(65+@j),N'j',2 set @j = @J + 1 end set @i = @i + 1 set @J = 0 end go CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6)) declare @ii int declare @jj int set @jj = 0 set @ii = 0 while @ii < 3 begin while @jj < 26 begin insert 产品生产计划 select char(65+@ii)+char(65+@jj),5 set @jj = @jj + 1 end set @ii = @ii + 1 set @jj = 0 end goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6)) DECLARE @p产品 varchar(10) DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR select 产品,产量 from 产品生产计划OPEN cp_Cursor FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0 BEGIN Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品 FETCH NEXT FROM cp_Cursor into @p产品,@p产量 END CLOSE cp_Cursor DEALLOCATE cp_Cursor GOdeclare @sqlhead varchar(8000) declare @sqlend varchar(8000) declare @sql1 varchar(8000) declare @sql2 varchar(8000) declare @sql3 varchar(8000) declare @sql4 varchar(8000) declare @i int declare @ic varchar(20)--生成数据处理临时表 select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then case when id=1 then 计划消耗 else 产量 end else 0 end)' into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from # print @i set @i=7800/@iupdate # set gid=id/@i select @i=max(gid) from #--生成数据处理语句 select @sqlhead='''select 材料=case when b.id=1 then 材料 else '''''''' end, 规格=case when b.id=1 then 规格 else '''''''' end, 单位=case when b.id=1 then 单位 else '''''''' end, 类别=case when b.id=1 then ''''计划消耗'''' else ''''产品产量'''' end, sum(case when id=1 then 计划消耗 else 产量 end) as 合计''' select @sqlend=''' from 产品生产物料采购计划,(select id=1 union all select id=2)b group by id,材料,规格,单位 order by case when b.id=1 then 材料 else 材料 end,b.id''' select @sql1='' select @sql2='select ' select @sql3='' select @sql4='' print @i while @i>=0 select @ic=cast(@i as varchar),@i=@i-1 select @sql1='@'+@ic+' varchar(8000),'+@sql1 select @sql2=@sql2+'@'+@ic+'='''',' select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3 select @sql4=@sql4+',@'+@icselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13) select @sql2=left(@sql2,len(@sql2)-1)+char(13) select @sql3=left(@sql3,len(@sql3)-1) select @sql4=substring(@sql4,2,8000)--执行 exec( @sql1+@sql2+@sql3+' exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')' ) --删除临时表 drop table # --*/drop TABLE BOM单 drop TABLE 产品生产计划 drop TABLE 产品生产物料采购计划
union还是包含在里面,就是不知@sqlhead,@sqlend 如何写,请继续修改一下,分帖已开,请接分。16:00再来。 ------------------------------ set nocount on CREATE TABLE BOM单(产品 varchar(10),材料 varchar(10),消耗 decimal(28,6)) declare @i int declare @J int set @J = 0 set @i = 0 while @I < 4 begin while @j < 26 begin insert bom单 select char(65+@i)+char(65+@j),N'a',2 insert bom单 select char(65+@i)+char(65+@j),N'b',2 insert bom单 select char(65+@i)+char(65+@j),N'c',2 insert bom单 select char(65+@i)+char(65+@j),N'd',2 insert bom单 select char(65+@i)+char(65+@j),N'e',2 insert bom单 select char(65+@i)+char(65+@j),N'f',2 insert bom单 select char(65+@i)+char(65+@j),N'g',2 insert bom单 select char(65+@i)+char(65+@j),N'h',2 insert bom单 select char(65+@i)+char(65+@j),N'i',2 insert bom单 select char(65+@i)+char(65+@j),N'j',2 set @j = @J + 1 end set @i = @i + 1 set @J = 0 end go CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6)) declare @ii int declare @jj int set @jj = 0 set @ii = 0 while @ii < 3 begin while @jj < 26 begin insert 产品生产计划 select char(65+@ii)+char(65+@jj),5 set @jj = @jj + 1 end set @ii = @ii + 1 set @jj = 0 end goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6)) DECLARE @p产品 varchar(10) DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR select 产品,产量 from 产品生产计划OPEN cp_Cursor FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0 BEGIN Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品 FETCH NEXT FROM cp_Cursor into @p产品,@p产量 END CLOSE cp_Cursor DEALLOCATE cp_Cursor GOdeclare @sqlhead varchar(8000) declare @sqlend varchar(8000) declare @sql1 varchar(8000) declare @sql2 varchar(8000) declare @sql3 varchar(8000) declare @sql4 varchar(8000) declare @i int declare @ic varchar(20)--生成数据处理临时表 select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then case when id=1 then 计划消耗 else 产量 end else 0 end)' into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from #set @i=7800/@iupdate # set gid=id/@i select @i=max(gid) from # --生成数据处理语句 select @sqlhead='''select 材料=case when b.id=1 then 材料 else '''''''' end, 规格=case when b.id=1 then 规格 else '''''''' end, 单位=case when b.id=1 then 单位 else '''''''' end, 类别=case when b.id=1 then ''''计划消耗'''' else ''''产品产量'''' end, sum(case when id=1 then 计划消耗 else 产量 end) as 合计''' select @sqlend=''' into #d from 产品生产物料采购计划,(select id=1 union all select id=2)b group by id,材料,规格,单位 order by case when b.id=1 then 材料 else 材料 end,b.id select distinct * from #d''' select @sql1='' select @sql2='select ' select @sql3='' select @sql4=''while @i>=0 select @ic=cast(@i as varchar),@i=@i-1 select @sql1='@'+@ic+' varchar(8000),'+@sql1 select @sql2=@sql2+'@'+@ic+'='''',' select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3 select @sql4=@sql4+',@'+@icselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13) select @sql2=left(@sql2,len(@sql2)-1)+char(13) select @sql3=left(@sql3,len(@sql3)-1) select @sql4=substring(@sql4,2,8000)--执行 exec( @sql1+@sql2+@sql3+' print @0 exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')') --删除临时表 drop table # --*/drop TABLE BOM单 drop TABLE 产品生产计划 drop TABLE 产品生产物料采购计划 /*材料 规格 单位 类别 合计 AA ... 产品产量 390.000000 5.000000 .. a NULL NULL 计划消耗 780.000000 10.000000 .. b NULL NULL 计划消耗 780.000000 10.000000 .. c NULL NULL 计划消耗 780.000000 10.000000 .. d NULL NULL 计划消耗 780.000000 10.000000 .. e NULL NULL 计划消耗 780.000000 10.000000 .. f NULL NULL 计划消耗 780.000000 10.000000 .. g NULL NULL 计划消耗 780.000000 10.000000 .. h NULL NULL 计划消耗 780.000000 10.000000 .. i NULL NULL 计划消耗 780.000000 10.000000 .. j NULL NULL 计划消耗 780.000000 10.000000 .. */
-- show 一下, 如果楼主用sql 2005, 则不用那么复杂, 下面的语句就可以搞定楼主的需求declare @sql varchar(max) set @sql = '' select @sql = @sql + ',' + quotename(产品) from 产品生产物料采购计划 group by 产品 order by 产品 set @sql=stuff(@sql,1,1,'') exec(' select 材料, 规格, 单位, 类别, 合计=(select sum(产量) from 产品生产物料采购计划), '+@sql+' from( select 产品,材料='''', 规格='''', 单位='''', 类别=''产品产量'',产量 from 产品生产物料采购计划 )data pivot( sum(产量) for 产品 in('+@sql+') )p union all select a.材料, 规格, 单位, 类别,b.合计,'+@sql+' from( select * from( select 产品,材料, 规格, 单位, 类别=''计划消耗'',计划消耗 from 产品生产物料采购计划 )data pivot( sum(计划消耗) for 产品 in('+@sql+') )p )a inner join( select 材料, 合计=sum(计划消耗) from 产品生产物料采购计划 group by 材料 )b on a.材料=b.材料 ')
--生成数据处理临时表 select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 计划消耗 else 0 end)' ,b=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 产量 else 0 end)' into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品 b=....应该是错误的,我还是不会改。
CREATE TABLE BOM单(产品 varchar(10),材料 varchar(10),消耗 decimal(28,6)) declare @i int declare @J int set @J = 0 set @i = 0 while @I < 4 begin while @j < 26 begin insert bom单 select char(65+@i)+char(65+@j),N'a',2 insert bom单 select char(65+@i)+char(65+@j),N'b',2 insert bom单 select char(65+@i)+char(65+@j),N'c',2 insert bom单 select char(65+@i)+char(65+@j),N'd',2 insert bom单 select char(65+@i)+char(65+@j),N'e',2 insert bom单 select char(65+@i)+char(65+@j),N'f',2 insert bom单 select char(65+@i)+char(65+@j),N'g',2 insert bom单 select char(65+@i)+char(65+@j),N'h',2 insert bom单 select char(65+@i)+char(65+@j),N'i',2 insert bom单 select char(65+@i)+char(65+@j),N'j',2 set @j = @J + 1 end set @i = @i + 1 set @J = 0 end go CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6)) declare @ii int declare @jj int set @jj = 0 set @ii = 0 while @ii < 3 begin while @jj < 26 begin insert 产品生产计划 select char(65+@ii)+char(65+@jj),5 set @jj = @jj + 1 end set @ii = @ii + 1 set @jj = 0 end goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6)) DECLARE @p产品 varchar(10) DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR select 产品,产量 from 产品生产计划OPEN cp_Cursor FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0 BEGIN Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品 FETCH NEXT FROM cp_Cursor into @p产品,@p产量 END CLOSE cp_Cursor DEALLOCATE cp_Cursor GOdeclare @sqlhead varchar(8000) declare @sqlend varchar(8000) declare @sql1 varchar(8000) declare @sql2 varchar(8000) declare @sql3 varchar(8000) declare @sql4 varchar(8000)declare @sqlhead_ varchar(8000) declare @sqlend_ varchar(8000) declare @sql1_ varchar(8000) declare @sql2_ varchar(8000) declare @sql3_ varchar(8000) declare @sql4_ varchar(8000)declare @i int declare @ic varchar(20)--生成数据处理临时表 select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 计划消耗 else 0 end)' ,b=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 产量 else 0 end)' into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from # print @i set @i=7800/@iupdate # set gid=id/@i select @i=max(gid) from #--生成数据处理语句 select @sqlhead=''' union all select 材料, 规格, 单位, 类别=''''计划消耗'''', sum(计划消耗) as 合计''' select @sqlend=''' from 产品生产物料采购计划 group by 材料,规格,单位''' select @sql1='' select @sql2='select ' select @sql3='' select @sql4=''select @sqlhead_='''select distinct 材料='''''''',规格='''''''', 单位='''''''', 类别=''''产品产量'''', sum(产量) as 合计''' select @sqlend_=''' from 产品生产物料采购计划 group by 材料,规格,单位 ''' select @sql1_='' select @sql2_='select ' select @sql3_='' select @sql4_='' print @i while @i>=0 begin select @ic=cast(@i as varchar),@i=@i-1 select @sql1='@'+@ic+' varchar(8000),'+@sql1 select @sql2=@sql2+'@'+@ic+'='''',' select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3 select @sql4=@sql4+',@'+@ic select @sql1_='@_'+@ic+' varchar(8000),'+@sql1_ select @sql2_=@sql2_+'@_'+@ic+'='''',' select @sql3_='select @_'+@ic+'=@_'+@ic+'+b from # where gid='+@ic+char(13)+@sql3_ select @sql4_=@sql4_+',@_'+@ic endselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13) select @sql2=left(@sql2,len(@sql2)-1)+char(13) select @sql3=left(@sql3,len(@sql3)-1) select @sql4=substring(@sql4,2,8000)select @sql1_='declare '+left(@sql1_,len(@sql1_)-1)+char(13) select @sql2_=left(@sql2_,len(@sql2_)-1)+char(13) select @sql3_=left(@sql3_,len(@sql3_)-1) select @sql4_=substring(@sql4_,2,8000)--执行 exec( @sql1_+@sql2_+@sql3_+' ' +@sql1+@sql2+@sql3+' exec('+@sqlhead_+'+'+@sql4_+'+'+@sqlend_+'+' +@sqlhead+'+'+@sql4+'+'+@sqlend+')' )--删除临时表 drop table # --*/ godrop TABLE BOM单 drop TABLE 产品生产计划 drop TABLE 产品生产物料采购计划 /*材料 规格 单位 类别 合计 AA ... 产品产量 390.000000 5.000000 .. a NULL NULL 计划消耗 780.000000 10.000000 .. b NULL NULL 计划消耗 780.000000 10.000000 .. c NULL NULL 计划消耗 780.000000 10.000000 .. d NULL NULL 计划消耗 780.000000 10.000000 .. e NULL NULL 计划消耗 780.000000 10.000000 .. f NULL NULL 计划消耗 780.000000 10.000000 .. g NULL NULL 计划消耗 780.000000 10.000000 .. h NULL NULL 计划消耗 780.000000 10.000000 .. i NULL NULL 计划消耗 780.000000 10.000000 .. j NULL NULL 计划消耗 780.000000 10.000000 .. */
happyflystone 已开接分帖,此帖也兑现,请查收,此帖我还想放一下。谢谢。通过此帖,收获不少!CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6)) declare @ii int declare @jj int set @jj = 0 set @ii = 0 while @ii < 3 begin while @jj < 26 begin insert 产品生产计划 select char(65+@ii)+char(65+@jj),5 set @jj = @jj + 1 end set @ii = @ii + 1 set @jj = 0 end问题:while @ii < 3 @ii最大只能5,如改为 while @ii < 6 运行出错,我就想到@ii=8不出错。
goCREATE TABLE BOM单(产品 varchar(10),材料 varchar(10),消耗 decimal(28,6))
declare @i int
declare @J int
set @J = 0
set @i = 0
while @I < 4
begin
while @j < 26
begin
insert bom单 select char(65+@i)+char(65+@j),N'a',2
insert bom单 select char(65+@i)+char(65+@j),N'b',2
insert bom单 select char(65+@i)+char(65+@j),N'c',2
insert bom单 select char(65+@i)+char(65+@j),N'd',2
insert bom单 select char(65+@i)+char(65+@j),N'e',2
insert bom单 select char(65+@i)+char(65+@j),N'f',2
insert bom单 select char(65+@i)+char(65+@j),N'g',2
insert bom单 select char(65+@i)+char(65+@j),N'h',2
insert bom单 select char(65+@i)+char(65+@j),N'i',2
insert bom单 select char(65+@i)+char(65+@j),N'j',2
set @j = @J + 1
end
set @i = @i + 1
set @J = 0
end
go
CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6))
declare @ii int
declare @jj int
set @jj = 0
set @ii = 0
while @ii < 3
begin
while @jj < 26
begin
insert 产品生产计划 select char(65+@ii)+char(65+@jj),5
set @jj = @jj + 1
end
set @ii = @ii + 1
set @jj = 0
end
goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6))
DECLARE @p产品 varchar(10)
DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR
select 产品,产量 from 产品生产计划OPEN cp_Cursor
FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品
FETCH NEXT FROM cp_Cursor into @p产品,@p产量
END
CLOSE cp_Cursor
DEALLOCATE cp_Cursor
GOdeclare @sqlhead varchar(8000)
declare @sqlend varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)
declare @i int
declare @ic varchar(20)--生成数据处理临时表
select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then case when id=1 then 计划消耗 else 产量 end else 0 end)'
into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from #
print @i
set @i=7800/@iupdate # set gid=id/@i
select @i=max(gid) from #--生成数据处理语句
select @sqlhead='''select 材料=case when b.id=1 then 材料 else '''''''' end,
规格=case when b.id=1 then 规格 else '''''''' end,
单位=case when b.id=1 then 单位 else '''''''' end,
类别=case when b.id=1 then ''''计划消耗'''' else ''''产品产量'''' end,
sum(case when id=1 then 计划消耗 else 产量 end) as 合计'''
select @sqlend=''' from 产品生产物料采购计划,(select id=1 union all select id=2)b group by id,材料,规格,单位 order by case when b.id=1 then 材料 else 材料 end,b.id'''
select @sql1=''
select @sql2='select '
select @sql3=''
select @sql4=''
print @i
while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
select @sql1='@'+@ic+' varchar(8000),'+@sql1
select @sql2=@sql2+'@'+@ic+'='''','
select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3
select @sql4=@sql4+',@'+@icselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
select @sql2=left(@sql2,len(@sql2)-1)+char(13)
select @sql3=left(@sql3,len(@sql3)-1)
select @sql4=substring(@sql4,2,8000)--执行
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)
--删除临时表
drop table #
--*/drop TABLE BOM单
drop TABLE 产品生产计划
drop TABLE 产品生产物料采购计划
-------- -------- -------- --------- ----------------- --------- -----
a NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
b NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
c NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
d NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
e NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
f NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
g NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
h NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
i NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
j NULL NULL 计划消耗 780.000000 10.000000 10.000000
产品产量 390.000000 5.000000 5.000000
结果应该是:
材料 规格 单位 类别 合计 AA BB
产品产量 390.000000 5.000000 5.000000
a NULL NULL 计划消耗 780.000000 10.000000 10.000000
b NULL NULL 计划消耗 780.000000 10.000000 10.000000
c NULL NULL 计划消耗 780.000000 10.000000 10.000000
d NULL NULL 计划消耗 780.000000 10.000000 10.000000
e NULL NULL 计划消耗 780.000000 10.000000 10.000000
f NULL NULL 计划消耗 780.000000 10.000000 10.000000
g NULL NULL 计划消耗 780.000000 10.000000 10.000000
h NULL NULL 计划消耗 780.000000 10.000000 10.000000
i NULL NULL 计划消耗 780.000000 10.000000 10.000000
j NULL NULL 计划消耗 780.000000 10.000000 10.000000
次问题是我上一次向你求教问题的继续,马上要去参加培训,一方面希望能满足以上样式,培训完,我再认真读一下程序和进行测试。
谢谢。
union还是包含在里面,就是不知@sqlhead,@sqlend 如何写,请继续修改一下,分帖已开,请接分。16:00再来。
------------------------------
set nocount on
CREATE TABLE BOM单(产品 varchar(10),材料 varchar(10),消耗 decimal(28,6))
declare @i int
declare @J int
set @J = 0
set @i = 0
while @I < 4
begin
while @j < 26
begin
insert bom单 select char(65+@i)+char(65+@j),N'a',2
insert bom单 select char(65+@i)+char(65+@j),N'b',2
insert bom单 select char(65+@i)+char(65+@j),N'c',2
insert bom单 select char(65+@i)+char(65+@j),N'd',2
insert bom单 select char(65+@i)+char(65+@j),N'e',2
insert bom单 select char(65+@i)+char(65+@j),N'f',2
insert bom单 select char(65+@i)+char(65+@j),N'g',2
insert bom单 select char(65+@i)+char(65+@j),N'h',2
insert bom单 select char(65+@i)+char(65+@j),N'i',2
insert bom单 select char(65+@i)+char(65+@j),N'j',2
set @j = @J + 1
end
set @i = @i + 1
set @J = 0
end
go
CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6))
declare @ii int
declare @jj int
set @jj = 0
set @ii = 0
while @ii < 3
begin
while @jj < 26
begin
insert 产品生产计划 select char(65+@ii)+char(65+@jj),5
set @jj = @jj + 1
end
set @ii = @ii + 1
set @jj = 0
end
goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6))
DECLARE @p产品 varchar(10)
DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR
select 产品,产量 from 产品生产计划OPEN cp_Cursor
FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品
FETCH NEXT FROM cp_Cursor into @p产品,@p产量
END
CLOSE cp_Cursor
DEALLOCATE cp_Cursor
GOdeclare @sqlhead varchar(8000)
declare @sqlend varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)
declare @i int
declare @ic varchar(20)--生成数据处理临时表
select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then case when id=1 then 计划消耗 else 产量 end else 0 end)'
into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from #set @i=7800/@iupdate # set gid=id/@i
select @i=max(gid) from #
--生成数据处理语句
select @sqlhead='''select 材料=case when b.id=1 then 材料 else '''''''' end,
规格=case when b.id=1 then 规格 else '''''''' end,
单位=case when b.id=1 then 单位 else '''''''' end,
类别=case when b.id=1 then ''''计划消耗'''' else ''''产品产量'''' end,
sum(case when id=1 then 计划消耗 else 产量 end) as 合计'''
select @sqlend=''' into #d from 产品生产物料采购计划,(select id=1 union all select id=2)b group by id,材料,规格,单位 order by case when b.id=1 then 材料 else 材料 end,b.id select distinct * from #d'''
select @sql1=''
select @sql2='select '
select @sql3=''
select @sql4=''while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
select @sql1='@'+@ic+' varchar(8000),'+@sql1
select @sql2=@sql2+'@'+@ic+'='''','
select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3
select @sql4=@sql4+',@'+@icselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
select @sql2=left(@sql2,len(@sql2)-1)+char(13)
select @sql3=left(@sql3,len(@sql3)-1)
select @sql4=substring(@sql4,2,8000)--执行
exec( @sql1+@sql2+@sql3+' print @0 exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')')
--删除临时表
drop table #
--*/drop TABLE BOM单
drop TABLE 产品生产计划
drop TABLE 产品生产物料采购计划
/*材料 规格 单位 类别 合计 AA ...
产品产量 390.000000 5.000000 ..
a NULL NULL 计划消耗 780.000000 10.000000 ..
b NULL NULL 计划消耗 780.000000 10.000000 ..
c NULL NULL 计划消耗 780.000000 10.000000 ..
d NULL NULL 计划消耗 780.000000 10.000000 ..
e NULL NULL 计划消耗 780.000000 10.000000 ..
f NULL NULL 计划消耗 780.000000 10.000000 ..
g NULL NULL 计划消耗 780.000000 10.000000 ..
h NULL NULL 计划消耗 780.000000 10.000000 ..
i NULL NULL 计划消耗 780.000000 10.000000 ..
j NULL NULL 计划消耗 780.000000 10.000000 ..
*/
邹大师请到接分帖(100五张),此帖星期五结!同时请问:@i=6,@@ii=6 为什么出错。
下班了,明天再见。
goCREATE TABLE BOM单(产品 varchar(10),材料 varchar(10),消耗 decimal(28,6))
declare @i int
declare @J int
set @J = 0
set @i = 0
while @I < 4
begin
while @j < 26
begin
insert bom单 select char(65+@i)+char(65+@j),N'a',2
insert bom单 select char(65+@i)+char(65+@j),N'b',2
insert bom单 select char(65+@i)+char(65+@j),N'c',2
insert bom单 select char(65+@i)+char(65+@j),N'd',2
insert bom单 select char(65+@i)+char(65+@j),N'e',2
insert bom单 select char(65+@i)+char(65+@j),N'f',2
insert bom单 select char(65+@i)+char(65+@j),N'g',2
insert bom单 select char(65+@i)+char(65+@j),N'h',2
insert bom单 select char(65+@i)+char(65+@j),N'i',2
insert bom单 select char(65+@i)+char(65+@j),N'j',2
set @j = @J + 1
end
set @i = @i + 1
set @J = 0
end
go
CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6))
declare @ii int
declare @jj int
set @jj = 0
set @ii = 0
while @ii < 3
begin
while @jj < 26
begin
insert 产品生产计划 select char(65+@ii)+char(65+@jj),5
set @jj = @jj + 1
end
set @ii = @ii + 1
set @jj = 0
end
goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6))
DECLARE @p产品 varchar(10)
DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR
select 产品,产量 from 产品生产计划OPEN cp_Cursor
FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品
FETCH NEXT FROM cp_Cursor into @p产品,@p产量
END
CLOSE cp_Cursor
DEALLOCATE cp_Cursor
GOdeclare @sqlhead varchar(8000)
declare @sqlend varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)declare @sqlhead_ varchar(8000)
declare @sqlend_ varchar(8000)
declare @sql1_ varchar(8000)
declare @sql2_ varchar(8000)
declare @sql3_ varchar(8000)
declare @sql4_ varchar(8000)declare @i int
declare @ic varchar(20)--生成数据处理临时表
select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 计划消耗 else 0 end)'
,b=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 产量 else 0 end)'
into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from #
print @i
set @i=7800/@iupdate # set gid=id/@i
select @i=max(gid) from #--生成数据处理语句
select @sqlhead=''' union all select 材料, 规格, 单位, 类别=''''计划消耗'''',
sum(计划消耗) as 合计'''
select @sqlend=''' from 产品生产物料采购计划 group by 材料,规格,单位'''
select @sql1=''
select @sql2='select '
select @sql3=''
select @sql4=''select @sqlhead_='''select 材料='''''''',规格='''''''', 单位='''''''', 类别=''''产品产量'''',
sum(产量) as 合计'''
select @sqlend_=''' from 产品生产物料采购计划'''
select @sql1_=''
select @sql2_='select '
select @sql3_=''
select @sql4_=''
print @i
while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
select @sql1='@'+@ic+' varchar(8000),'+@sql1
select @sql2=@sql2+'@'+@ic+'='''','
select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3
select @sql4=@sql4+',@'+@ic, select @sql1_='@'+@ic+' varchar(8000),'+@sql1_
select @sql2_=@sql2_+'@'+@ic+'='''','
select @sql3_='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3_
select @sql4_=@sql4_+',@'+@ic
select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
select @sql2=left(@sql2,len(@sql2)-1)+char(13)
select @sql3=left(@sql3,len(@sql3)-1)
select @sql4=substring(@sql4,2,8000)select @sql1_='declare '+left(@sql1_,len(@sql1_)-1)+char(13)
select @sql2_=left(@sql2_,len(@sql2_)-1)+char(13)
select @sql3_=left(@sql3_,len(@sql3_)-1)
select @sql4_=substring(@sql4_,2,8000)
--执行
exec(
@sql1_+@sql2_+@sql3_+'
exec('+@sqlhead_+'+'+@sql4_+'+'+@sqlend_+')'@sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)
--删除临时表
drop table #
--*/drop TABLE BOM单
drop TABLE 产品生产计划
drop TABLE 产品生产物料采购计划
不明白你的意思
goCREATE TABLE BOM单(产品 varchar(10),材料 varchar(10),消耗 decimal(28,6))
declare @i int
declare @J int
set @J = 0
set @i = 0
while @I < 4
begin
while @j < 26
begin
insert bom单 select char(65+@i)+char(65+@j),N'a',2
insert bom单 select char(65+@i)+char(65+@j),N'b',2
insert bom单 select char(65+@i)+char(65+@j),N'c',2
insert bom单 select char(65+@i)+char(65+@j),N'd',2
insert bom单 select char(65+@i)+char(65+@j),N'e',2
insert bom单 select char(65+@i)+char(65+@j),N'f',2
insert bom单 select char(65+@i)+char(65+@j),N'g',2
insert bom单 select char(65+@i)+char(65+@j),N'h',2
insert bom单 select char(65+@i)+char(65+@j),N'i',2
insert bom单 select char(65+@i)+char(65+@j),N'j',2
set @j = @J + 1
end
set @i = @i + 1
set @J = 0
end
go
CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6))
declare @ii int
declare @jj int
set @jj = 0
set @ii = 0
while @ii < 3
begin
while @jj < 26
begin
insert 产品生产计划 select char(65+@ii)+char(65+@jj),5
set @jj = @jj + 1
end
set @ii = @ii + 1
set @jj = 0
end
goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6))
DECLARE @p产品 varchar(10)
DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR
select 产品,产量 from 产品生产计划OPEN cp_Cursor
FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品
FETCH NEXT FROM cp_Cursor into @p产品,@p产量
END
CLOSE cp_Cursor
DEALLOCATE cp_Cursor
GOdeclare @sqlhead varchar(8000)
declare @sqlend varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)declare @sqlhead_ varchar(8000)
declare @sqlend_ varchar(8000)
declare @sql1_ varchar(8000)
declare @sql2_ varchar(8000)
declare @sql3_ varchar(8000)
declare @sql4_ varchar(8000)declare @i int
declare @ic varchar(20)--生成数据处理临时表
select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 计划消耗 else 0 end)'
,b=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 产量 else 0 end)'
into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from #
print @i
set @i=7800/@iupdate # set gid=id/@i
select @i=max(gid) from #--生成数据处理语句
select @sqlhead=''' union all select 材料, 规格, 单位, 类别=''''计划消耗'''',
sum(计划消耗) as 合计'''
select @sqlend=''' from 产品生产物料采购计划 group by 材料,规格,单位'''
select @sql1=''
select @sql2='select '
select @sql3=''
select @sql4=''select @sqlhead_='''select 材料='''''''',规格='''''''', 单位='''''''', 类别=''''产品产量'''',
sum(产量) as 合计'''
select @sqlend_=''' from 产品生产物料采购计划'''
select @sql1_=''
select @sql2_='select '
select @sql3_=''
select @sql4_=''
print @i
while @i>=0
begin
select @ic=cast(@i as varchar),@i=@i-1
select @sql1='@'+@ic+' varchar(8000),'+@sql1
select @sql2=@sql2+'@'+@ic+'='''','
select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3
select @sql4=@sql4+',@'+@ic select @sql1_='@_'+@ic+' varchar(8000),'+@sql1_
select @sql2_=@sql2_+'@_'+@ic+'='''','
select @sql3_='select @_'+@ic+'=@_'+@ic+'+b from # where gid='+@ic+char(13)+@sql3_
select @sql4_=@sql4_+',@_'+@ic
endselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
select @sql2=left(@sql2,len(@sql2)-1)+char(13)
select @sql3=left(@sql3,len(@sql3)-1)
select @sql4=substring(@sql4,2,8000)select @sql1_='declare '+left(@sql1_,len(@sql1_)-1)+char(13)
select @sql2_=left(@sql2_,len(@sql2_)-1)+char(13)
select @sql3_=left(@sql3_,len(@sql3_)-1)
select @sql4_=substring(@sql4_,2,8000)
--执行
exec(
@sql1_+@sql2_+@sql3_+'
'
+@sql1+@sql2+@sql3+'
exec('+@sqlhead_+'+'+@sql4_+'+'+@sqlend_+'+'
+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)
--删除临时表
drop table #
--*/
godrop TABLE BOM单
drop TABLE 产品生产计划
drop TABLE 产品生产物料采购计划
楼主提到的"@i=6,@@ii=6 为什么出错。" 应该与此有关.
set @sql = ''
select @sql = @sql + ',' + quotename(产品)
from 产品生产物料采购计划
group by 产品
order by 产品
set @sql=stuff(@sql,1,1,'')
exec('
select 材料, 规格, 单位, 类别,
合计=(select sum(产量) from 产品生产物料采购计划),
'+@sql+'
from(
select 产品,材料='''', 规格='''', 单位='''', 类别=''产品产量'',产量
from 产品生产物料采购计划
)data
pivot(
sum(产量)
for 产品 in('+@sql+')
)p
union all
select a.材料, 规格, 单位, 类别,b.合计,'+@sql+'
from(
select *
from(
select 产品,材料, 规格, 单位, 类别=''计划消耗'',计划消耗
from 产品生产物料采购计划
)data
pivot(
sum(计划消耗)
for 产品 in('+@sql+')
)p
)a inner join(
select 材料, 合计=sum(计划消耗)
from 产品生产物料采购计划
group by 材料
)b on a.材料=b.材料
')
select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 计划消耗 else 0 end)'
,b=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 产量 else 0 end)'
into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品
b=....应该是错误的,我还是不会改。
declare @i int
declare @J int
set @J = 0
set @i = 0
while @I < 4
begin
while @j < 26
begin
insert bom单 select char(65+@i)+char(65+@j),N'a',2
insert bom单 select char(65+@i)+char(65+@j),N'b',2
insert bom单 select char(65+@i)+char(65+@j),N'c',2
insert bom单 select char(65+@i)+char(65+@j),N'd',2
insert bom单 select char(65+@i)+char(65+@j),N'e',2
insert bom单 select char(65+@i)+char(65+@j),N'f',2
insert bom单 select char(65+@i)+char(65+@j),N'g',2
insert bom单 select char(65+@i)+char(65+@j),N'h',2
insert bom单 select char(65+@i)+char(65+@j),N'i',2
insert bom单 select char(65+@i)+char(65+@j),N'j',2
set @j = @J + 1
end
set @i = @i + 1
set @J = 0
end
go
CREATE TABLE 产品生产计划( 产品 varchar(10),产量 decimal(28,6))
declare @ii int
declare @jj int
set @jj = 0
set @ii = 0
while @ii < 3
begin
while @jj < 26
begin
insert 产品生产计划 select char(65+@ii)+char(65+@jj),5
set @jj = @jj + 1
end
set @ii = @ii + 1
set @jj = 0
end
goCREATE TABLE 产品生产物料采购计划( 产品 varchar(10),产量 decimal(28,6),材料 varchar(60),规格 varchar(60),单位 varchar(4),消耗 decimal(28,6),计划消耗 decimal(28,6))
DECLARE @p产品 varchar(10)
DECLARE @p产量 decimal(28,6)DECLARE cp_Cursor CURSOR FOR
select 产品,产量 from 产品生产计划OPEN cp_Cursor
FETCH NEXT FROM cp_Cursor into @p产品,@p产量WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into 产品生产物料采购计划(产品,产量,材料,消耗,计划消耗) select @p产品,@p产量,bo.材料,bo.消耗,bo.消耗*@p产量 from bom单 bo where bo.产品=@p产品
FETCH NEXT FROM cp_Cursor into @p产品,@p产量
END
CLOSE cp_Cursor
DEALLOCATE cp_Cursor
GOdeclare @sqlhead varchar(8000)
declare @sqlend varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)declare @sqlhead_ varchar(8000)
declare @sqlend_ varchar(8000)
declare @sql1_ varchar(8000)
declare @sql2_ varchar(8000)
declare @sql3_ varchar(8000)
declare @sql4_ varchar(8000)declare @i int
declare @ic varchar(20)--生成数据处理临时表
select id=identity(int,0,1),gid=0 ,a=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 计划消耗 else 0 end)'
,b=',['+产品 +']=sum(case 产品 when ''' +产品+''' then 产量 else 0 end)'
into # from(select distinct 产品 from 产品生产物料采购计划) a order by 产品select @i=max(len(a)) from #
print @i
set @i=7800/@iupdate # set gid=id/@i
select @i=max(gid) from #--生成数据处理语句
select @sqlhead=''' union all select 材料, 规格, 单位, 类别=''''计划消耗'''',
sum(计划消耗) as 合计'''
select @sqlend=''' from 产品生产物料采购计划 group by 材料,规格,单位'''
select @sql1=''
select @sql2='select '
select @sql3=''
select @sql4=''select @sqlhead_='''select distinct 材料='''''''',规格='''''''', 单位='''''''', 类别=''''产品产量'''',
sum(产量) as 合计'''
select @sqlend_=''' from 产品生产物料采购计划 group by 材料,规格,单位 '''
select @sql1_=''
select @sql2_='select '
select @sql3_=''
select @sql4_=''
print @i
while @i>=0
begin
select @ic=cast(@i as varchar),@i=@i-1
select @sql1='@'+@ic+' varchar(8000),'+@sql1
select @sql2=@sql2+'@'+@ic+'='''','
select @sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic+char(13)+@sql3
select @sql4=@sql4+',@'+@ic select @sql1_='@_'+@ic+' varchar(8000),'+@sql1_
select @sql2_=@sql2_+'@_'+@ic+'='''','
select @sql3_='select @_'+@ic+'=@_'+@ic+'+b from # where gid='+@ic+char(13)+@sql3_
select @sql4_=@sql4_+',@_'+@ic
endselect @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
select @sql2=left(@sql2,len(@sql2)-1)+char(13)
select @sql3=left(@sql3,len(@sql3)-1)
select @sql4=substring(@sql4,2,8000)select @sql1_='declare '+left(@sql1_,len(@sql1_)-1)+char(13)
select @sql2_=left(@sql2_,len(@sql2_)-1)+char(13)
select @sql3_=left(@sql3_,len(@sql3_)-1)
select @sql4_=substring(@sql4_,2,8000)--执行
exec(
@sql1_+@sql2_+@sql3_+'
'
+@sql1+@sql2+@sql3+'
exec('+@sqlhead_+'+'+@sql4_+'+'+@sqlend_+'+'
+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
)--删除临时表
drop table #
--*/
godrop TABLE BOM单
drop TABLE 产品生产计划
drop TABLE 产品生产物料采购计划
/*材料 规格 单位 类别 合计 AA ...
产品产量 390.000000 5.000000 ..
a NULL NULL 计划消耗 780.000000 10.000000 ..
b NULL NULL 计划消耗 780.000000 10.000000 ..
c NULL NULL 计划消耗 780.000000 10.000000 ..
d NULL NULL 计划消耗 780.000000 10.000000 ..
e NULL NULL 计划消耗 780.000000 10.000000 ..
f NULL NULL 计划消耗 780.000000 10.000000 ..
g NULL NULL 计划消耗 780.000000 10.000000 ..
h NULL NULL 计划消耗 780.000000 10.000000 ..
i NULL NULL 计划消耗 780.000000 10.000000 ..
j NULL NULL 计划消耗 780.000000 10.000000 ..
*/
declare @ii int
declare @jj int
set @jj = 0
set @ii = 0
while @ii < 3
begin
while @jj < 26
begin
insert 产品生产计划 select char(65+@ii)+char(65+@jj),5
set @jj = @jj + 1
end
set @ii = @ii + 1
set @jj = 0
end问题:while @ii < 3
@ii最大只能5,如改为 while @ii < 6 运行出错,我就想到@ii=8不出错。