谁能帮我优化下这个存储过程比例分配表,设置参数
CREATE PROCEDURE zz_zly1_dtb @cxrq datetime as
---查询初步结果SELECT TOP 100 PERCENT b.s_whouse, ' ' AS gd,itemdata.prdclass, SUM(a.qty) AS sqty,SUM(a.qty) AS tqty,SUM(a.qty) AS qty into zz_zly1_temp3
FROM senddet a INNER JOIN
sendmst b ON a.sysno = b.sysno INNER JOIN
itemdata ON a.itemno = itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.carddt = @cxrq) AND (b.s_whouse <> '107')
GROUP BY b.s_whouse, itemdata.prdclass
ORDER BY b.s_whouse, itemdata.prdclassSELECT TOP 100 PERCENT b.t_whouse, ' ' AS gd, dbo.itemdata.prdclass, SUM(a.qty) AS qty into zz_zly1_temp0
FROM dbo.sbackdet a INNER JOIN
dbo.sbackmst b ON a.sysno = b.sysno INNER JOIN
dbo.itemdata ON a.itemno = dbo.itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.carddt = @cxrq) AND (b.t_whouse <> '107')
GROUP BY b.t_whouse, dbo.itemdata.prdclass
ORDER BY b.t_whouse, dbo.itemdata.prdclassSELECT TOP 100 PERCENT b.s_whouse, ' ' AS gd,itemdata.prdclass,SUM(a.qty) AS sqty,SUM(a.qty) AS tqty,SUM(a.qty) AS qty into zz_zly1_temp4
FROM senddet a INNER JOIN
sendmst b ON a.sysno = b.sysno INNER JOIN
itemdata ON a.itemno = itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.s_whouse <> '107') AND
(MONTH(b.carddt) = MONTH(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.s_whouse, itemdata.prdclass
ORDER BY b.s_whouse, itemdata.prdclassSELECT TOP 100 PERCENT b.t_whouse, ' ' AS gd, dbo.itemdata.prdclass, SUM(a.qty) AS qty into zz_zly1_temp1
FROM dbo.sbackdet a INNER JOIN
dbo.sbackmst b ON a.sysno = b.sysno INNER JOIN
dbo.itemdata ON a.itemno = dbo.itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.t_whouse <> '107') AND
(MONTH(b.carddt) = MONTH(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.t_whouse, dbo.itemdata.prdclass
ORDER BY b.t_whouse, dbo.itemdata.prdclassSELECT TOP 100 PERCENT b.s_whouse, ' ' AS gd,itemdata.prdclass, SUM(a.qty) AS sqty,SUM(a.qty) AS tqty,SUM(a.qty) AS qty into zz_zly1_temp5
FROM senddet a INNER JOIN
sendmst b ON a.sysno = b.sysno INNER JOIN
itemdata ON a.itemno = itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.s_whouse <> '107') AND
(YEAR(b.carddt) = YEAR(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.s_whouse, itemdata.prdclass
ORDER BY b.s_whouse, itemdata.prdclassSELECT TOP 100 PERCENT b.t_whouse, ' ' AS gd, dbo.itemdata.prdclass, SUM(a.qty) AS qty into zz_zly1_temp2
FROM dbo.sbackdet a INNER JOIN
dbo.sbackmst b ON a.sysno = b.sysno INNER JOIN
dbo.itemdata ON a.itemno = dbo.itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.t_whouse <> '107') AND
(YEAR(b.carddt) = YEAR(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.t_whouse, dbo.itemdata.prdclass
ORDER BY b.t_whouse, dbo.itemdata.prdclassupdate zz_zly1_temp3 set tqty = 0,qty = 0
update zz_zly1_temp4 set tqty = 0,qty = 0
update zz_zly1_temp5 set tqty = 0,qty = 0
CREATE PROCEDURE zz_zly1_dtb @cxrq datetime as
---查询初步结果SELECT TOP 100 PERCENT b.s_whouse, ' ' AS gd,itemdata.prdclass, SUM(a.qty) AS sqty,SUM(a.qty) AS tqty,SUM(a.qty) AS qty into zz_zly1_temp3
FROM senddet a INNER JOIN
sendmst b ON a.sysno = b.sysno INNER JOIN
itemdata ON a.itemno = itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.carddt = @cxrq) AND (b.s_whouse <> '107')
GROUP BY b.s_whouse, itemdata.prdclass
ORDER BY b.s_whouse, itemdata.prdclassSELECT TOP 100 PERCENT b.t_whouse, ' ' AS gd, dbo.itemdata.prdclass, SUM(a.qty) AS qty into zz_zly1_temp0
FROM dbo.sbackdet a INNER JOIN
dbo.sbackmst b ON a.sysno = b.sysno INNER JOIN
dbo.itemdata ON a.itemno = dbo.itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.carddt = @cxrq) AND (b.t_whouse <> '107')
GROUP BY b.t_whouse, dbo.itemdata.prdclass
ORDER BY b.t_whouse, dbo.itemdata.prdclassSELECT TOP 100 PERCENT b.s_whouse, ' ' AS gd,itemdata.prdclass,SUM(a.qty) AS sqty,SUM(a.qty) AS tqty,SUM(a.qty) AS qty into zz_zly1_temp4
FROM senddet a INNER JOIN
sendmst b ON a.sysno = b.sysno INNER JOIN
itemdata ON a.itemno = itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.s_whouse <> '107') AND
(MONTH(b.carddt) = MONTH(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.s_whouse, itemdata.prdclass
ORDER BY b.s_whouse, itemdata.prdclassSELECT TOP 100 PERCENT b.t_whouse, ' ' AS gd, dbo.itemdata.prdclass, SUM(a.qty) AS qty into zz_zly1_temp1
FROM dbo.sbackdet a INNER JOIN
dbo.sbackmst b ON a.sysno = b.sysno INNER JOIN
dbo.itemdata ON a.itemno = dbo.itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.t_whouse <> '107') AND
(MONTH(b.carddt) = MONTH(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.t_whouse, dbo.itemdata.prdclass
ORDER BY b.t_whouse, dbo.itemdata.prdclassSELECT TOP 100 PERCENT b.s_whouse, ' ' AS gd,itemdata.prdclass, SUM(a.qty) AS sqty,SUM(a.qty) AS tqty,SUM(a.qty) AS qty into zz_zly1_temp5
FROM senddet a INNER JOIN
sendmst b ON a.sysno = b.sysno INNER JOIN
itemdata ON a.itemno = itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.s_whouse <> '107') AND
(YEAR(b.carddt) = YEAR(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.s_whouse, itemdata.prdclass
ORDER BY b.s_whouse, itemdata.prdclassSELECT TOP 100 PERCENT b.t_whouse, ' ' AS gd, dbo.itemdata.prdclass, SUM(a.qty) AS qty into zz_zly1_temp2
FROM dbo.sbackdet a INNER JOIN
dbo.sbackmst b ON a.sysno = b.sysno INNER JOIN
dbo.itemdata ON a.itemno = dbo.itemdata.itemno
WHERE (b.ischeck = '1') AND (b.iscancell = '0') AND (b.t_whouse <> '107') AND
(YEAR(b.carddt) = YEAR(@cxrq)) AND (b.carddt <= @cxrq)
GROUP BY b.t_whouse, dbo.itemdata.prdclass
ORDER BY b.t_whouse, dbo.itemdata.prdclassupdate zz_zly1_temp3 set tqty = 0,qty = 0
update zz_zly1_temp4 set tqty = 0,qty = 0
update zz_zly1_temp5 set tqty = 0,qty = 0
解决方案 »
- 怎样求数据的跨度
- 如何从Access中提取最新的日期(在Access表中日期字段是string类型的。)
- 请指教一下sql2000中,这个解发器如何写,谢谢
- 分析器里 运行两个存储过程时 需要间隔一段时间
- 求 SQL语句
- sqlserver2000、sqlserver2005、sqlserver2008卸载问题,难道非要重新装系统吗
- 如何查看SQL server2000 的版本信息,我安装了sp4补丁(Microsoft SQL Server 2000 - 8.00.194)后1433端口还是没开
- 这个存储过程如何写?日期相关的。
- 动态SQL存储过程如何显示在水晶报表中?
- 字段更新问题
- 麻烦帮忙看下这个存储过程的问题呀
- 多条数据合并为一条,但查询速度过慢,有没有方法提高查询速度?
---回填股东单位
update zz_zly1_temp0 set gd = 'jl' where t_whouse = '101' or t_whouse = '102' or t_whouse = '103'
update zz_zly1_temp0 set gd = 'ph' where t_whouse = '104' or t_whouse = '106'
update zz_zly1_temp0 set gd = 'gh' where t_whouse = '105'
update zz_zly1_temp0 set gd = 'sw' where t_whouse like '2%' update zz_zly1_temp1 set gd = 'jl' where t_whouse = '101' or t_whouse = '102' or t_whouse = '103'
update zz_zly1_temp1 set gd = 'ph' where t_whouse = '104' or t_whouse = '106'
update zz_zly1_temp1 set gd = 'gh' where t_whouse = '105'
update zz_zly1_temp1 set gd = 'sw' where t_whouse like '2%' update zz_zly1_temp2 set gd = 'jl' where t_whouse = '101' or t_whouse = '102' or t_whouse = '103'
update zz_zly1_temp2 set gd = 'ph' where t_whouse = '104' or t_whouse = '106'
update zz_zly1_temp2 set gd = 'gh' where t_whouse = '105'
update zz_zly1_temp2 set gd = 'sw' where t_whouse like '2%' update zz_zly1_temp3 set gd = 'jl' where s_whouse = '101' or s_whouse = '102' or s_whouse = '103'
update zz_zly1_temp3 set gd = 'ph' where s_whouse = '104' or s_whouse = '106'
update zz_zly1_temp3 set gd = 'gh' where s_whouse = '105'
update zz_zly1_temp3 set gd = 'sw' where s_whouse like '2%' update zz_zly1_temp4 set gd = 'jl' where s_whouse = '101' or s_whouse = '102' or s_whouse = '103'
update zz_zly1_temp4 set gd = 'ph' where s_whouse = '104' or s_whouse = '106'
update zz_zly1_temp4 set gd = 'gh' where s_whouse = '105'
update zz_zly1_temp4 set gd = 'sw' where s_whouse like '2%' update zz_zly1_temp5 set gd = 'jl' where s_whouse = '101' or s_whouse = '102' or s_whouse = '103'
update zz_zly1_temp5 set gd = 'ph' where s_whouse = '104' or s_whouse = '106'
update zz_zly1_temp5 set gd = 'gh' where s_whouse = '105'
update zz_zly1_temp5 set gd = 'sw' where s_whouse like '2%'
---按股东单位再次汇总
SELECT TOP 100 PERCENT gd,prdclass,SUM(sqty) AS sqty, SUM(tqty) AS tqty,SUM(qty) AS qty into zz_zly1_temp6
FROM zz_zly1_temp3
GROUP BY gd,prdclass
ORDER BY gd,prdclassSELECT TOP 100 PERCENT gd,prdclass,SUM(sqty) AS sqty, SUM(tqty) AS tqty,SUM(qty) AS qty into zz_zly1_temp7
FROM zz_zly1_temp4
GROUP BY gd,prdclass
ORDER BY gd,prdclassSELECT TOP 100 PERCENT gd,prdclass,SUM(sqty) AS sqty, SUM(tqty) AS tqty,SUM(qty) AS qty into zz_zly1_temp8
FROM zz_zly1_temp5
GROUP BY gd,prdclass
ORDER BY gd,prdclassSELECT TOP 100 PERCENT gd,prdclass,SUM(qty) AS qty into zz_zly1_temp10
FROM zz_zly1_temp0
GROUP BY gd,prdclass
ORDER BY gd,prdclassSELECT TOP 100 PERCENT gd,prdclass,SUM(qty) AS qty into zz_zly1_temp11
FROM zz_zly1_temp1
GROUP BY gd,prdclass
ORDER BY gd,prdclassSELECT TOP 100 PERCENT gd,prdclass,SUM(qty) AS qty into zz_zly1_temp12
FROM zz_zly1_temp2
GROUP BY gd,prdclass
ORDER BY gd,prdclassupdate zz_zly1_temp6 set tqty = zz_zly1_temp10.qty from zz_zly1_temp10 where zz_zly1_temp6.gd = zz_zly1_temp10.gd and zz_zly1_temp6.prdclass = zz_zly1_temp10.prdclass
update zz_zly1_temp7 set tqty = zz_zly1_temp11.qty from zz_zly1_temp11 where zz_zly1_temp7.gd = zz_zly1_temp11.gd and zz_zly1_temp7.prdclass = zz_zly1_temp11.prdclass
update zz_zly1_temp8 set tqty = zz_zly1_temp12.qty from zz_zly1_temp12 where zz_zly1_temp8.gd = zz_zly1_temp12.gd and zz_zly1_temp8.prdclass = zz_zly1_temp12.prdclassupdate zz_zly1_temp6 set qty = sqty-tqty
update zz_zly1_temp7 set qty = sqty-tqty
update zz_zly1_temp8 set qty = sqty-tqty
---创建结果表表结构
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zz_zly1_dtbb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[zz_zly1_dtbb]CREATE TABLE [dbo].[zz_zly1_dtbb] (
[prdclass] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[prdname] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[jl_y] [decimal](18, 3) NULL ,
[jl_m] [decimal](18, 3) NULL ,
[jl_d] [decimal](18, 3) NULL ,
[jl_l] [decimal](18, 4) NULL ,
[ph_y] [decimal](18, 3) NULL ,
[ph_m] [decimal](18, 3) NULL ,
[ph_d] [decimal](18, 3) NULL ,
[ph_l] [decimal](18, 4) NULL ,
[gh_y] [decimal](18, 3) NULL ,
[gh_m] [decimal](18, 3) NULL ,
[gh_d] [decimal](18, 3) NULL ,
[gh_l] [decimal](18, 4) NULL ,
[sw_y] [decimal](18, 3) NULL ,
[sw_m] [decimal](18, 3) NULL ,
[hj_y] [decimal](18, 3) NULL ,
[hj_m] [decimal](18, 3) NULL ,
[hj_d] [decimal](18, 3) NULL ,
[mx] [decimal](18, 3) NULL,
[jl_q] [decimal](18, 3) NULL,
[ph_q] [decimal](18, 3) NULL,
[gh_q] [decimal](18, 3) NULL
) ON [PRIMARY]---根据基础表回填数据
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01001', '新4号铵梯油炸药(常规)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01002', '新4号铵梯油炸药(异型)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01005', '二级岩石乳化炸药(常规)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01006', '二级岩石乳化炸药(异型)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01007', '三级煤矿乳化炸药')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01010', '1号--3号铵油炸药')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01003', '岩石膨化硝铵炸药(常规)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01004', '岩石膨化硝铵炸药(异型)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01008', '岩石粉状乳化炸药(常规)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01009', '岩石粉状乳化炸药(异型)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01011', '3#煤矿铵梯炸药')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01012', '煤矿粉状乳化炸药(常规)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01013', '包装乳化炸药')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01014', '水胶炸药')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01777', '粉状炸药')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01888', '乳化炸药')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '01999', '炸 药(吨)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '02001', '电雷管(万发)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '02002', '火雷管(万发)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '02003', '塑料导爆管雷管(万发)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '03003', '塑料导爆管(万米)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '03002', '导爆索(万米)')
insert into zz_zly1_dtbb (prdclass,prdname) values ( '03001', '导火索(万米)')update zz_zly1_dtbb set jl_y=0
update zz_zly1_dtbb set jl_m=0
update zz_zly1_dtbb set jl_d=0
update zz_zly1_dtbb set jl_l=0
update zz_zly1_dtbb set ph_y=0
update zz_zly1_dtbb set ph_m=0
update zz_zly1_dtbb set ph_d=0
update zz_zly1_dtbb set ph_l=0
update zz_zly1_dtbb set gh_y=0
update zz_zly1_dtbb set gh_m=0
update zz_zly1_dtbb set gh_d=0
update zz_zly1_dtbb set gh_l=0
update zz_zly1_dtbb set sw_y=0
update zz_zly1_dtbb set sw_m=0
update zz_zly1_dtbb set hj_y=0
update zz_zly1_dtbb set hj_m=0
update zz_zly1_dtbb set hj_d=0
update zz_zly1_dtbb set mx=0
update zz_zly1_dtbb set jl_q=0
update zz_zly1_dtbb set ph_q=0
update zz_zly1_dtbb set gh_q=0update zz_zly1_dtbb set jl_d = zz_zly1_temp6.qty from zz_zly1_temp6 where zz_zly1_dtbb.prdclass = zz_zly1_temp6.prdclass and zz_zly1_temp6.gd = 'jl'
update zz_zly1_dtbb set jl_m = zz_zly1_temp7.qty from zz_zly1_temp7 where zz_zly1_dtbb.prdclass = zz_zly1_temp7.prdclass and zz_zly1_temp7.gd = 'jl'
update zz_zly1_dtbb set jl_y = zz_zly1_temp8.qty from zz_zly1_temp8 where zz_zly1_dtbb.prdclass = zz_zly1_temp8.prdclass and zz_zly1_temp8.gd = 'jl'update zz_zly1_dtbb set ph_d = zz_zly1_temp6.qty from zz_zly1_temp6 where zz_zly1_dtbb.prdclass = zz_zly1_temp6.prdclass and zz_zly1_temp6.gd = 'ph'
update zz_zly1_dtbb set ph_m = zz_zly1_temp7.qty from zz_zly1_temp7 where zz_zly1_dtbb.prdclass = zz_zly1_temp7.prdclass and zz_zly1_temp7.gd = 'ph'
update zz_zly1_dtbb set ph_y = zz_zly1_temp8.qty from zz_zly1_temp8 where zz_zly1_dtbb.prdclass = zz_zly1_temp8.prdclass and zz_zly1_temp8.gd = 'ph'update zz_zly1_dtbb set gh_d = zz_zly1_temp6.qty from zz_zly1_temp6 where zz_zly1_dtbb.prdclass = zz_zly1_temp6.prdclass and zz_zly1_temp6.gd = 'gh'
update zz_zly1_dtbb set gh_m = zz_zly1_temp7.qty from zz_zly1_temp7 where zz_zly1_dtbb.prdclass = zz_zly1_temp7.prdclass and zz_zly1_temp7.gd = 'gh'
update zz_zly1_dtbb set gh_y = zz_zly1_temp8.qty from zz_zly1_temp8 where zz_zly1_dtbb.prdclass = zz_zly1_temp8.prdclass and zz_zly1_temp8.gd = 'gh'update zz_zly1_dtbb set sw_m = zz_zly1_temp7.qty from zz_zly1_temp7 where zz_zly1_dtbb.prdclass = zz_zly1_temp7.prdclass and zz_zly1_temp7.gd = 'sw'
update zz_zly1_dtbb set sw_y = zz_zly1_temp8.qty from zz_zly1_temp8 where zz_zly1_dtbb.prdclass = zz_zly1_temp8.prdclass and zz_zly1_temp8.gd = 'sw'
---回填合计数据
update zz_zly1_dtbb set hj_d = jl_d+ph_d+gh_d
update zz_zly1_dtbb set hj_m = jl_m+ph_m+gh_m+sw_m
update zz_zly1_dtbb set hj_y = jl_y+ph_y+gh_y+sw_ySELECT SUM(jl_y) AS jl_y, SUM(jl_m) AS jl_m, SUM(jl_d) AS jl_d, SUM(ph_y) AS ph_y,
SUM(ph_m) AS ph_m, SUM(ph_d) AS ph_d, SUM(gh_y) AS gh_y, SUM(gh_m) AS gh_m,
SUM(gh_d) AS gh_d,SUM(sw_y) AS sw_y, SUM(sw_m) AS sw_m, SUM(hj_y) AS hj_y, SUM(hj_m) AS hj_m, SUM(hj_d)
AS hj_d into zz_zly1_temp13
FROM zz_zly1_dtbb
WHERE prdclass = '01001' or prdclass ='01002' or prdclass ='01003' or prdclass ='01004' or prdclass ='01010' or prdclass ='01011'update zz_zly1_dtbb set jl_y =zz_zly1_temp13.jl_y, jl_m =zz_zly1_temp13.jl_m, jl_d =zz_zly1_temp13.jl_d, ph_y =zz_zly1_temp13.ph_y,
ph_m =zz_zly1_temp13.ph_m, ph_d =zz_zly1_temp13.ph_d, gh_y =zz_zly1_temp13.gh_y, gh_m =zz_zly1_temp13.gh_m,
gh_d =zz_zly1_temp13.gh_d, sw_y =zz_zly1_temp13.sw_y, sw_m =zz_zly1_temp13.sw_m,hj_y =zz_zly1_temp13.hj_y, hj_m =zz_zly1_temp13.hj_m, hj_d
=zz_zly1_temp13.hj_d from zz_zly1_temp13 where prdclass = '01777'SELECT SUM(jl_y) AS jl_y, SUM(jl_m) AS jl_m, SUM(jl_d) AS jl_d, SUM(ph_y) AS ph_y,
SUM(ph_m) AS ph_m, SUM(ph_d) AS ph_d, SUM(gh_y) AS gh_y, SUM(gh_m) AS gh_m,
SUM(gh_d) AS gh_d,SUM(sw_y) AS sw_y, SUM(sw_m) AS sw_m, SUM(hj_y) AS hj_y, SUM(hj_m) AS hj_m, SUM(hj_d)
AS hj_d into zz_zly1_temp14
FROM zz_zly1_dtbb
WHERE prdclass = '01005' or prdclass ='01006' or prdclass ='01007' or prdclass ='01008' or prdclass ='01009' or prdclass ='01012'update zz_zly1_dtbb set jl_y =zz_zly1_temp14.jl_y, jl_m =zz_zly1_temp14.jl_m, jl_d =zz_zly1_temp14.jl_d, ph_y =zz_zly1_temp14.ph_y,
ph_m =zz_zly1_temp14.ph_m, ph_d =zz_zly1_temp14.ph_d, gh_y =zz_zly1_temp14.gh_y, gh_m =zz_zly1_temp14.gh_m,
gh_d =zz_zly1_temp14.gh_d, sw_y =zz_zly1_temp14.sw_y, sw_m =zz_zly1_temp14.sw_m,hj_y =zz_zly1_temp14.hj_y, hj_m =zz_zly1_temp14.hj_m, hj_d
=zz_zly1_temp14.hj_d from zz_zly1_temp14 where prdclass = '01888'
SELECT SUM(jl_y) AS jl_y, SUM(jl_m) AS jl_m, SUM(jl_d) AS jl_d, SUM(ph_y) AS ph_y,
SUM(ph_m) AS ph_m, SUM(ph_d) AS ph_d, SUM(gh_y) AS gh_y, SUM(gh_m) AS gh_m,
SUM(gh_d) AS gh_d,SUM(sw_y) AS sw_y, SUM(sw_m) AS sw_m, SUM(hj_y) AS hj_y, SUM(hj_m) AS hj_m, SUM(hj_d)
AS hj_d into zz_zly1_temp9
FROM zz_zly1_dtbb
WHERE (prdclass < '01777')update zz_zly1_dtbb set jl_y =zz_zly1_temp9.jl_y, jl_m =zz_zly1_temp9.jl_m, jl_d =zz_zly1_temp9.jl_d, ph_y =zz_zly1_temp9.ph_y,
ph_m =zz_zly1_temp9.ph_m, ph_d =zz_zly1_temp9.ph_d, gh_y =zz_zly1_temp9.gh_y, gh_m =zz_zly1_temp9.gh_m,
gh_d =zz_zly1_temp9.gh_d, sw_y =zz_zly1_temp9.sw_y, sw_m =zz_zly1_temp9.sw_m,hj_y =zz_zly1_temp9.hj_y, hj_m =zz_zly1_temp9.hj_m, hj_d
=zz_zly1_temp9.hj_d from zz_zly1_temp9 where prdclass = '01999'
---****************
DECLARE @Max float,@jlRemender float,@phRemender float,@ghRemender float,@jlSum float,@phSum float,@ghSum float
select @jlSum=jl_y,@phSum=ph_y,@ghSum=gh_y from zz_zly1_temp13set @Max=@jlSum/0.4
if @phSum / 0.3 > @Max
set @Max=@phSum / 0.3if @ghSum / 0.3 > @Max
set @Max=@ghSum / 0.3set @jlRemender=@jlSum - @Max * 0.4
set @phRemender= @phSum -@Max * 0.3
set @ghRemender=@ghSum -@Max * 0.3update zz_zly1_dtbb set mx = @Max where prdclass = '01777'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '01777'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '01777'
update zz_zly1_dtbb set gh_q=@ghRemender where prdclass = '01777'select @jlSum=jl_y,@phSum=ph_y,@ghSum=gh_y from zz_zly1_temp14set @Max=@jlSum/0.4
if @phSum / 0.3 > @Max
set @Max=@phSum / 0.3if @ghSum / 0.3 > @Max
set @Max=@ghSum / 0.3set @jlRemender=@jlSum - @Max * 0.4
set @phRemender= @phSum -@Max * 0.3
set @ghRemender=@ghSum -@Max * 0.3update zz_zly1_dtbb set mx = @Max where prdclass = '01888'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '01888'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '01888'
update zz_zly1_dtbb set gh_q=@ghRemender where prdclass = '01888'select @jlSum=jl_y,@phSum=ph_y,@ghSum=gh_y from zz_zly1_temp9 set @Max=@jlSum/0.4
if @phSum / 0.3 > @Max
set @Max=@phSum / 0.3if @ghSum / 0.3 > @Max
set @Max=@ghSum / 0.3set @jlRemender=@jlSum - @Max * 0.4
set @phRemender= @phSum -@Max * 0.3
set @ghRemender=@ghSum -@Max * 0.3update zz_zly1_dtbb set mx = @Max where prdclass = '01999'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '01999'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '01999'
update zz_zly1_dtbb set gh_q=@ghRemender where prdclass = '01999'
--电雷管
select @jlSum=jl_y,@phSum=ph_y from zz_zly1_dtbb where prdclass='02001'set @Max=@jlSum / 0.4
if @phSum / 0.6 > @Max
set @Max=@phSum / 0.6set @jlRemender=@jlSum - @Max * 0.4
set @phRemender= @phSum -@Max * 0.6
update zz_zly1_dtbb set mx = @Max where prdclass = '02001'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '02001'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '02001'--火雷管
select @jlSum=jl_y,@phSum=ph_y from zz_zly1_dtbb where prdclass='02002'set @Max=@jlSum / 0.85
if @phSum / 0.15 > @Max
set @Max=@phSum / 0.15set @jlRemender=@jlSum - @Max * 0.85
set @phRemender=@phSum - @Max * 0.15update zz_zly1_dtbb set mx = @Max where prdclass = '02002'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '02002'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '02002' --塑料导爆管雷管
select @jlSum=jl_y,@phSum=ph_y from zz_zly1_dtbb where prdclass='02003'set @Max=@jlSum / 0.74
if @phSum / 0.26 > @Max
set @Max=@phSum / 0.26set @jlRemender=@jlSum - @Max * 0.74
set @phRemender=@phSum - @Max * 0.26update zz_zly1_dtbb set mx = @Max where prdclass = '02003'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '02003'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '02003'
--导火索
select @jlSum=jl_y,@phSum=ph_y from zz_zly1_dtbb where prdclass='03001'set @Max=@jlSum / 0.555
if @phSum / 0.445 > @Max
set @Max=@phSum / 0.445set @jlRemender=@jlSum - @Max * 0.555
set @phRemender=@phSum - @Max * 0.445update zz_zly1_dtbb set mx = @Max where prdclass = '03001'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '03001'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '03001'
--塑料导爆管
select @jlSum=jl_y,@phSum=ph_y from zz_zly1_dtbb where prdclass='03003'set @Max=@jlSum / 0.74
if @phSum / 0.26 > @Max
set @Max=@phSum / 0.26set @jlRemender=@jlSum - @Max * 0.74
set @phRemender=@phSum - @Max * 0.26update zz_zly1_dtbb set mx = @Max where prdclass = '03003'
update zz_zly1_dtbb set jl_q=@jlRemender where prdclass = '03003'
update zz_zly1_dtbb set ph_q=@phRemender where prdclass = '03003'
---回填累计百分比
update zz_zly1_dtbb set jl_l = jl_y/(hj_y-sw_y) where jl_y <>0
update zz_zly1_dtbb set ph_l = ph_y/(hj_y-sw_y) where ph_y <>0
update zz_zly1_dtbb set gh_l = gh_y/(hj_y-sw_y) where gh_y <>0
---
select * from zz_zly1_dtbb where prdclass not between '01001' and '01014' order by prdclass,prdnamedrop table zz_zly1_temp0
drop table zz_zly1_temp1
drop table zz_zly1_temp2
drop table zz_zly1_temp3
drop table zz_zly1_temp4
drop table zz_zly1_temp5
drop table zz_zly1_temp6
drop table zz_zly1_temp7
drop table zz_zly1_temp8
drop table zz_zly1_temp9
drop table zz_zly1_temp10
drop table zz_zly1_temp11
drop table zz_zly1_temp12
drop table zz_zly1_temp13
drop table zz_zly1_temp14
GO
谁能帮我优化下这个存储过程,先谢谢了