createprocproc_dssf_sale_calculate@time_yearchar(4),@time_montchar(2),@unit_codevarchar(50)-
create proc proc_dssf_sale_calculate @time_year char(4),@time_mont char(2),@unit_code varchar(50) --创建一个存储过程并添加3个输入参数
AS
/****************************************************************************************************/
/*程序名:统计售电情况的本月数据之后,更新表DSSF_SALE,计算累计、季度、年度、丰枯水期、电价等计算项 */
/*Author:邵春宇 2002-9-27 */
/****************************************************************************************************/
--参数说明:@time_year:年份;@time_mont:月份( 不含丰水期枯水期以及季度等汇总一级数据)
--@unit_code:执行存储过程的单位代码
declare @sum_mont char(2),@leve int --定义变量
--计算@time_mont的累计数据
update DSSF_SALE --按照指定的输入变量值为条件为表更新值
set
CUME_POWE=
(select sum(B.POWE) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_MOnE=
(select sum(B.MONE) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_NUMB=
(select sum(B.NUMB) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_COSI=
(select sum(B.COSI) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_COSD=
(select sum(B.COSD) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_CAPA=
(select sum(B.CAPA) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_NEED=
(select sum(B.NEED) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_FGPM=
(select sum(B.FGPM) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_PREF_POWE=
(select sum(B.PREF_POWE) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_PREF_MOnE=
(select sum(B.PREF_MONE) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_PLAN_POWE=
(select sum(B.PLAN_POWE) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_PROG_POWE=
(select sum(B.PROG_POWE) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code),
CUME_PLAN_MOnE=
(select sum(B.PLAN_MONE) from DSSF_SALE B where B.TIME_YEAR=@time_year and
B.TIME_MONT>='01' and B.TIME_MONT<=@time_mont and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@time_mont and A.UNIT_CODE=@unit_code
18 个解决方案
create table #DSSF_SALE --创建一个临时表
(
TIME_YEAR char(4) not null,
TIME_MONT char(2) not null,
UNIT_CODE varchar(50) not null,
SALE_CODE varchar(50) not null,
POWE numeric(18,4) null ,
CUME_POWE numeric(18,4) null ,
LAST_POWE numeric(18,4) null ,
PREV_POWE numeric(18,4) null ,
LAST_CUME_POWE numeric(18,4) null ,
MONE numeric(18,4) null ,
CUME_MONE numeric(18,4) null ,
LAST_MONE numeric(18,4) null ,
PREV_MONE numeric(18,4) null ,
LAST_CUME_MONE numeric(18,4) null ,
PRIC numeric(12,6) null ,
CUME_PRIC numeric(12,6) null ,
LAST_PRIC numeric(12,6) null ,
PREV_PRIC numeric(12,6) null ,
LAST_CUME_PRIC numeric(12,6) null ,
NUMB numeric(18,4) null ,
CUME_NUMB numeric(18,4) null ,
LAST_NUMB numeric(18,4) null ,
PREV_NUMB numeric(18,4) null ,
LAST_CUME_NUMB numeric(18,4) null ,
NUMB_PRIC numeric(12,6) null ,
LAST_NUMB_PRIC numeric(12,6) null ,
CUME_NUMB_PRIC numeric(12,6) null ,
LAST_CUME_NUMB_PRIC numeric(12,6) null ,
PREV_NUMB_PRIC numeric(12,6) null ,
COSI numeric(18,4) null ,
CUME_COSI numeric(18,4) null ,
LAST_COSI numeric(18,4) null ,
PREV_COSI numeric(18,4) null ,
LAST_CUME_COSI numeric(18,4) null ,
COSD numeric(18,4) null ,
CUME_COSD numeric(18,4) null ,
LAST_COSD numeric(18,4) null ,
PREV_COSD numeric(18,4) null ,
LAST_CUME_COSD numeric(18,4) null ,
COS_PRIC numeric(12,6) null ,
LAST_COS_PRIC numeric(12,6) null ,
PREV_COS_PRIC numeric(12,6) null ,
CUME_COS_PRIC numeric(12,6) null ,
LAST_CUME_COS_PRIC numeric(12,6) null ,
CAPA numeric(18,4) null ,
CUME_CAPA numeric(18,4) null ,
LAST_CAPA numeric(18,4) null ,
PREV_CAPA numeric(18,4) null ,
LAST_CUME_CAPA numeric(18,4) null ,
NEED numeric(18,4) null ,
CUME_NEED numeric(18,4) null ,
LAST_NEED numeric(18,4) null ,
PREV_NEED numeric(18,4) null ,
LAST_CUME_NEED numeric(18,4) null ,
BASE_PRIC numeric(12,6) null ,
LAST_BASE_PRIC numeric(12,6) null ,
PREV_BASE_PRIC numeric(12,6) null ,
CUME_BASE_PRIC numeric(12,6) null ,
LAST_CUME_BASE_PRIC numeric(12,6) null ,
FGPM numeric(18,4) null ,
CUME_FGPM numeric(18,4) null ,
LAST_FGPM numeric(18,4) null ,
PREV_FGPM numeric(18,4) null ,
LAST_CUME_FGPM numeric(18,4) null ,
FGP_PRIC numeric(12,6) null ,
LAST_FGP_PRIC numeric(12,6) null ,
PREV_FGP_PRIC numeric(12,6) null ,
CUME_FGP_PRIC numeric(12,6) null ,
LAST_CUME_FGP_PRIC numeric(12,6) null ,
PREF_POWE numeric(18,4) null ,
CUME_PREF_POWE numeric(18,4) null ,
LAST_PREF_POWE numeric(18,4) null ,
PREV_PREF_POWE numeric(18,4) null ,
LAST_CUME_PREF_POWE numeric(18,4) null ,
PREF_MONE numeric(18,4) null ,
CUME_PREF_MONE numeric(18,4) null ,
LAST_PREF_MONE numeric(18,4) null ,
PREV_PREF_MONE numeric(18,4) null ,
LAST_CUME_PREF_MONE numeric(18,4) null ,
PREF_PRIC numeric(12,6) null ,
CUME_PREF_PRIC numeric(12,6) null ,
LAST_PREF_PRIC numeric(12,6) null ,
PREV_PREF_PRIC numeric(12,6) null ,
LAST_CUME_PREF_PRIC numeric(12,6) null ,
PLAN_POWE numeric(18,4) null ,
YEAR_PLAN_POWE numeric(18,4) null ,
LAST_YEAR_POWE numeric(18,4) null ,
PLAN_PRIC numeric(12,6) null ,
CUME_PLAN_POWE numeric(18,4) null ,
CUME_PLAN_PRIC numeric(12,6) null ,
PLAN_MONE numeric(18,4) null ,
CUME_PLAN_MONE numeric(18,4) null ,
YEAR_PLAN_MONE numeric(18,4) null ,
LAST_YEAR_MONE numeric(18,4) null ,
PROG_POWE numeric(18,4) null ,
CUME_PROG_POWE numeric(18,4) null ,
ADD_MONEY numeric(18,4) null ,
LAST_ADD_MONEY numeric(18,4) null ,
PREV_ADD_MONEY numeric(18,4) null ,
CUME_ADD_MONEY numeric(18,4) null ,
LAST_CUME_ADD_MONEY numeric(18,4) null ,
FS_PRIC numeric(12,6) null ,
KS_PRIC numeric(12,6) null
)
declare c1 cursor for --创建一个游标
select distinct SUM_MONT,LEVE from DSSS_TIME_RELATION
where MOnT=@time_mont order by LEVE ASC
open c1 --打开游标
while(@@sqlstatus=0) --按照指定的条件进行循环
begin
fetch c1 into @sum_mont,@leve --装取行值
insert into #DSSF_SALE --插入值
(
TIME_YEAR,TIME_MONT,UNIT_CODE,SALE_CODE,
POWE, CUME_POWE, LAST_POWE, PREV_POWE, LAST_CUME_POWE,
MONE, CUME_MONE, LAST_MONE, PREV_MONE, LAST_CUME_MONE,
PRIC, CUME_PRIC, LAST_PRIC, PREV_PRIC, LAST_CUME_PRIC,
NUMB, CUME_NUMB, LAST_NUMB, PREV_NUMB, LAST_CUME_NUMB,
NUMB_PRIC, LAST_NUMB_PRIC, CUME_NUMB_PRIC, LAST_CUME_NUMB_PRIC,
PREV_NUMB_PRIC, COSI, CUME_COSI, LAST_COSI, PREV_COSI,
LAST_CUME_COSI, COSD, CUME_COSD, LAST_COSD, PREV_COSD,
LAST_CUME_COSD, COS_PRIC, LAST_COS_PRIC, PREV_COS_PRIC,
CUME_COS_PRIC, LAST_CUME_COS_PRIC, CAPA, CUME_CAPA,
LAST_CAPA, PREV_CAPA, LAST_CUME_CAPA, NEED, CUME_NEED, LAST_NEED,
PREV_NEED, LAST_CUME_NEED, BASE_PRIC, LAST_BASE_PRIC, PREV_BASE_PRIC,
CUME_BASE_PRIC, LAST_CUME_BASE_PRIC, FGPM, CUME_FGPM, LAST_FGPM,
PREV_FGPM, LAST_CUME_FGPM, FGP_PRIC, LAST_FGP_PRIC, PREV_FGP_PRIC,
CUME_FGP_PRIC, LAST_CUME_FGP_PRIC, PREF_POWE, CUME_PREF_POWE,
LAST_PREF_POWE, PREV_PREF_POWE, LAST_CUME_PREF_POWE, PREF_MONE,
CUME_PREF_MONE, LAST_PREF_MONE, PREV_PREF_MONE, LAST_CUME_PREF_MONE,
PREF_PRIC, CUME_PREF_PRIC, LAST_PREF_PRIC, PREV_PREF_PRIC,
LAST_CUME_PREF_PRIC,PLAN_POWE,YEAR_PLAN_POWE,LAST_YEAR_POWE,PLAN_PRIC,
CUME_PLAN_POWE,CUME_PLAN_PRIC,PLAN_MONE,CUME_PLAN_MONE,YEAR_PLAN_MONE,
LAST_YEAR_MONE,PROG_POWE,CUME_PROG_POWE,ADD_MONEY,LAST_ADD_MONEY,
PREV_ADD_MONEY,CUME_ADD_MONEY,LAST_CUME_ADD_MONEY,FS_PRIC,KS_PRIC
)
SELECT ---按照变量的值和一个存储参数以及两个表满足取行变量的值进行插入
@time_year, @sum_mont, @unit_code, A.DIME_VALU_CODE,
B.POWE, B.CUME_POWE, B.LAST_POWE, B.PREV_POWE, B.LAST_CUME_POWE,
B.MONE, B.CUME_MONE, B.LAST_MONE, B.PREV_MONE, B.LAST_CUME_MONE,
B.PRIC, B.CUME_PRIC, B.LAST_PRIC, B.PREV_PRIC, B.LAST_CUME_PRIC,
B.NUMB, B.CUME_NUMB, B.LAST_NUMB, B.PREV_NUMB, B.LAST_CUME_NUMB,
B.NUMB_PRIC, B.LAST_NUMB_PRIC, B.CUME_NUMB_PRIC, B.LAST_CUME_NUMB_PRIC,
B.PREV_NUMB_PRIC, B.COSI, B.CUME_COSI, B.LAST_COSI, B.PREV_COSI,
B.LAST_CUME_COSI, B.COSD, B.CUME_COSD, B.LAST_COSD, B.PREV_COSD,
B.LAST_CUME_COSD, B.COS_PRIC, B.LAST_COS_PRIC, B.PREV_COS_PRIC,
B.CUME_COS_PRIC, B.LAST_CUME_COS_PRIC, B.CAPA, B.CUME_CAPA,
B.LAST_CAPA, B.PREV_CAPA, B.LAST_CUME_CAPA, B.NEED, B.CUME_NEED, B.LAST_NEED,
B.PREV_NEED, B.LAST_CUME_NEED, B.BASE_PRIC, B.LAST_BASE_PRIC, B.PREV_BASE_PRIC,
B.CUME_BASE_PRIC, B.LAST_CUME_BASE_PRIC, B.FGPM, B.CUME_FGPM, B.LAST_FGPM,
B.PREV_FGPM, B.LAST_CUME_FGPM, B.FGP_PRIC, B.LAST_FGP_PRIC, B.PREV_FGP_PRIC,
B.CUME_FGP_PRIC, B.LAST_CUME_FGP_PRIC, B.PREF_POWE, B.CUME_PREF_POWE,
B.LAST_PREF_POWE, B.PREV_PREF_POWE, B.LAST_CUME_PREF_POWE, B.PREF_MONE,
B.CUME_PREF_MONE, B.LAST_PREF_MONE, B.PREV_PREF_MONE, B.LAST_CUME_PREF_MONE,
B.PREF_PRIC, B.CUME_PREF_PRIC, B.LAST_PREF_PRIC, B.PREV_PREF_PRIC,
B.LAST_CUME_PREF_PRIC,B.PLAN_POWE,B.YEAR_PLAN_POWE,B.LAST_YEAR_POWE,B.PLAN_PRIC,
B.CUME_PLAN_POWE,B.CUME_PLAN_PRIC,B.PLAN_MONE,B.CUME_PLAN_MONE,B.YEAR_PLAN_MONE,
B.LAST_YEAR_MONE,B.PROG_POWE,B.CUME_PROG_POWE,B.ADD_MONEY,B.LAST_ADD_MONEY,
B.PREV_ADD_MONEY,B.CUME_ADD_MONEY,B.LAST_CUME_ADD_MONEY,B.FS_PRIC,B.KS_PRIC
FROM dbo.DSSD_SALE A, dbo.DSSF_SALE B
WHERE ( B.SALE_CODE =* A.DIME_VALU_CODE ) AND (B.UNIT_CODE=@unit_code) and
TIME_YEAR=@time_year and TIME_MOnT=@sum_mont
delete from DSSF_SALE where TIME_YEAR=@time_year and TIME_MOnT=@sum_mont and UNIT_CODE=@unit_code
insert into DSSF_SALE --按照表指定的值插入变量
(TIME_YEAR,TIME_MONT,UNIT_CODE,SALE_CODE,
POWE, CUME_POWE, LAST_POWE, PREV_POWE, LAST_CUME_POWE,
MONE, CUME_MONE, LAST_MONE, PREV_MONE, LAST_CUME_MONE,
PRIC, CUME_PRIC, LAST_PRIC, PREV_PRIC, LAST_CUME_PRIC,
NUMB, CUME_NUMB, LAST_NUMB, PREV_NUMB, LAST_CUME_NUMB,
NUMB_PRIC, LAST_NUMB_PRIC, CUME_NUMB_PRIC, LAST_CUME_NUMB_PRIC,
PREV_NUMB_PRIC, COSI, CUME_COSI, LAST_COSI, PREV_COSI,
LAST_CUME_COSI, COSD, CUME_COSD, LAST_COSD, PREV_COSD,
LAST_CUME_COSD, COS_PRIC, LAST_COS_PRIC, PREV_COS_PRIC,
CUME_COS_PRIC, LAST_CUME_COS_PRIC, CAPA, CUME_CAPA,
LAST_CAPA, PREV_CAPA, LAST_CUME_CAPA, NEED, CUME_NEED, LAST_NEED,
PREV_NEED, LAST_CUME_NEED, BASE_PRIC, LAST_BASE_PRIC, PREV_BASE_PRIC,
CUME_BASE_PRIC, LAST_CUME_BASE_PRIC, FGPM, CUME_FGPM, LAST_FGPM,
PREV_FGPM, LAST_CUME_FGPM, FGP_PRIC, LAST_FGP_PRIC, PREV_FGP_PRIC,
CUME_FGP_PRIC, LAST_CUME_FGP_PRIC, PREF_POWE, CUME_PREF_POWE,
LAST_PREF_POWE, PREV_PREF_POWE, LAST_CUME_PREF_POWE, PREF_MONE,
CUME_PREF_MONE, LAST_PREF_MONE, PREV_PREF_MONE, LAST_CUME_PREF_MONE,
PREF_PRIC, CUME_PREF_PRIC, LAST_PREF_PRIC, PREV_PREF_PRIC,
LAST_CUME_PREF_PRIC,PLAN_POWE,YEAR_PLAN_POWE,LAST_YEAR_POWE,PLAN_PRIC,
CUME_PLAN_POWE,CUME_PLAN_PRIC,PLAN_MONE,CUME_PLAN_MONE,YEAR_PLAN_MONE,
LAST_YEAR_MONE,PROG_POWE,CUME_PROG_POWE,ADD_MONEY,LAST_ADD_MONEY,
PREV_ADD_MONEY,CUME_ADD_MONEY,LAST_CUME_ADD_MONEY,FS_PRIC,KS_PRIC)
select
TIME_YEAR,TIME_MONT,UNIT_CODE,SALE_CODE,
POWE, CUME_POWE, LAST_POWE, PREV_POWE, LAST_CUME_POWE,
MONE, CUME_MONE, LAST_MONE, PREV_MONE, LAST_CUME_MONE,
PRIC, CUME_PRIC, LAST_PRIC, PREV_PRIC, LAST_CUME_PRIC,
NUMB, CUME_NUMB, LAST_NUMB, PREV_NUMB, LAST_CUME_NUMB,
NUMB_PRIC, LAST_NUMB_PRIC, CUME_NUMB_PRIC, LAST_CUME_NUMB_PRIC,
PREV_NUMB_PRIC, COSI, CUME_COSI, LAST_COSI, PREV_COSI,
LAST_CUME_COSI, COSD, CUME_COSD, LAST_COSD, PREV_COSD,
LAST_CUME_COSD, COS_PRIC, LAST_COS_PRIC, PREV_COS_PRIC,
CUME_COS_PRIC, LAST_CUME_COS_PRIC, CAPA, CUME_CAPA,
LAST_CAPA, PREV_CAPA, LAST_CUME_CAPA, NEED, CUME_NEED, LAST_NEED,
PREV_NEED, LAST_CUME_NEED, BASE_PRIC, LAST_BASE_PRIC, PREV_BASE_PRIC,
CUME_BASE_PRIC, LAST_CUME_BASE_PRIC, FGPM, CUME_FGPM, LAST_FGPM,
PREV_FGPM, LAST_CUME_FGPM, FGP_PRIC, LAST_FGP_PRIC, PREV_FGP_PRIC,
CUME_FGP_PRIC, LAST_CUME_FGP_PRIC, PREF_POWE, CUME_PREF_POWE,
LAST_PREF_POWE, PREV_PREF_POWE, LAST_CUME_PREF_POWE, PREF_MONE,
CUME_PREF_MONE, LAST_PREF_MONE, PREV_PREF_MONE, LAST_CUME_PREF_MONE,
PREF_PRIC, CUME_PREF_PRIC, LAST_PREF_PRIC, PREV_PREF_PRIC,
LAST_CUME_PREF_PRIC,PLAN_POWE,YEAR_PLAN_POWE,LAST_YEAR_POWE,PLAN_PRIC,
CUME_PLAN_POWE,CUME_PLAN_PRIC,PLAN_MONE,CUME_PLAN_MONE,YEAR_PLAN_MONE,
LAST_YEAR_MONE,PROG_POWE,CUME_PROG_POWE,ADD_MONEY,LAST_ADD_MONEY,
PREV_ADD_MONEY,CUME_ADD_MONEY,LAST_CUME_ADD_MONEY,FS_PRIC,KS_PRIC
from #DSSF_SALE
delete from #DSSF_SALE
update DSSF_SALE --按照指定的值为检索条件为此表中的值进行更新
set
POWE=(--售电量
select
SUM(ISNULL(POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
MOnE=(--售电收入
select
SUM(ISNULL(MONE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
NUMB=(--电度电费
select
SUM(ISNULL(NUMB,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
COSI=(--力调增
select
SUM(ISNULL(COSI,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
COSD=(--力调减
select
SUM(ISNULL(COSD,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
CAPA=(--容量电费
select
SUM(ISNULL(CAPA,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
NEED=(--需量电费
select
SUM(ISNULL(NEED,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
UPDATE DSSF_SALE
set
FGPM=(--峰谷增收电费
select
SUM(ISNULL(FGPM,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
PREF_POWE=(--优惠电量
select
SUM(ISNULL(PREF_POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
PREF_MOnE=(--优惠电费
select
SUM(ISNULL(PREF_MONE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
PLAN_POWE=(--计划电量
select
SUM(ISNULL(PLAN_POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
PLAN_MOnE=(--计划电费
select
SUM(ISNULL(PLAN_MONE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
),
PROG_POWE=(--预测电量
select
SUM(ISNULL(PROG_POWE,0))
from DSSF_SALE B,DSSS_TIME_RELATION C
where B.TIME_MOnT=C.MONT and B.UNIT_CODE=@unit_code and C.SUM_MOnT=@sum_mont
and B.SALE_CODE=A.SALE_CODE and B.TIME_YEAR=@time_year
)
from DSSF_SALE A
where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
update DSSF_SALE
set
CUME_POWE=
(
select B.CUME_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_MOnE=
(
select B.CUME_MONE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_NUMB=
(
select B.CUME_NUMB from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_COSI=
(
select B.CUME_COSI from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_COSD=
(
select B.CUME_COSD from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_CAPA=
(
select B.CUME_CAPA from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_NEED=
(
select B.CUME_NEED from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_FGPM=
(
select B.CUME_FGPM from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PREF_POWE=
(
select B.CUME_PREF_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PREF_MOnE=
(
select B.CUME_PREF_MONE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PLAN_POWE=
(
select B.CUME_PLAN_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PROG_POWE=
(
select B.CUME_PROG_POWE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
),
CUME_PLAN_MOnE=
(
select B.CUME_PLAN_MONE from DSSF_SALE B where B.TIME_YEAR=@time_year and B.TIME_MOnT=@time_mont
and B.SALE_CODE=A.SALE_CODE and B.UNIT_CODE=@unit_code
)
from DSSF_SALE A where A.TIME_YEAR=@time_year and A.TIME_MOnT=@sum_mont and A.UNIT_CODE=@unit_code
end --结素循环
close c1 --关闭游标
distinct SUM_MONT from DSSS_TIME_RELATION where MOnT=@time_mont)
)
deallocate cursor c1
update DSSF_SALE --按照指定的值进行更新表并且进行强制替换
set
PRIC=isnull(convert(numeric(12,6), MONE/POWE),0),
NUMB_PRIC=isnull(convert(numeric(12,6), NUMB/POWE),0),
COS_PRIC=isnull(convert(numeric(12,6), (isnull(COSI,0)-isnull(COSD,0))/POWE),0),
BASE_PRIC=isnull(convert(numeric(12,6), (isnull(CAPA,0)+isnull(NEED,0))/POWE),0),
FGP_PRIC=isnull(convert(numeric(12,6), FGPM/POWE),0),
PREF_PRIC=isnull(convert(numeric(12,6), PREF_MONE/POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and POWE>0 and
(TIME_MOnT=@time_mont or TIME_MONT in
(select distinct SUM_MONT from DSSS_TIME_RELATION where MOnT=@time_mont)
)
update DSSF_SALE
set
CUME_PRIC=isnull(convert(numeric(12,6), CUME_MONE/CUME_POWE),0),
CUME_NUMB_PRIC=isnull(convert(numeric(12,6), CUME_NUMB/CUME_POWE),0),
CUME_COS_PRIC=isnull(convert(numeric(12,6), (isnull(CUME_COSI,0)-isnull(CUME_COSD,0))/CUME_POWE),0),
CUME_BASE_PRIC=isnull(convert(numeric(12,6), (isnull(CUME_CAPA,0)+isnull(CUME_NEED,0))/CUME_POWE),0),
CUME_FGP_PRIC=isnull(convert(numeric(12,6), CUME_FGPM/CUME_POWE),0),
CUME_PREF_PRIC=isnull(convert(numeric(12,6), CUME_PREF_MONE/CUME_POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and CUME_POWE>0 and
(TIME_MOnT=@time_mont or TIME_MONT in
(select distinct SUM_MONT from DSSS_TIME_RELATION where MOnT=@time_mont)
)
update DSSF_SALE
set
CUME_PLAN_PRIC=isnull(convert(numeric(12,6), CUME_PLAN_MONE/CUME_PLAN_POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and CUME_PLAN_POWE>0 and
(TIME_MOnT=@time_mont or TIME_MONT in
(select distinct SUM_MONT from DSSS_TIME_RELATION where MOnT=@time_mont)
)
update DSSF_SALE
set
PLAN_PRIC=isnull(convert(numeric(12,6), PLAN_MONE/PLAN_POWE),0)
from DSSF_SALE where TIME_YEAR=@time_year and UNIT_CODE=@unit_code and PLAN_POWE>0 and
(TIME_MOnT=@time_mont or TIME_MONT in
(select
update 表a
set
表a的需要更新的列=
(select sum(表b的求和的列) from 表B where 表b的条件列1=@输入参数 and 表b的外键列=表a的主键列),
......
其实存储过程就是一句,不知楼主贴出这么多东东来会有谁愿意去仔细看。
update 表a
set
表a的需要更新的列=
(select sum(表b的求和的列) from 表B where 表b的条件列1=@输入参数 and 表b的外键列=表a的主键列),
......
我只是想知道逻辑结构,我也知道够长了,可工作中的不都是怎么长么。你不愿看算了,可你不能打击我对CSDN的信心,知道么。谢谢大力的几次照顾了,我只是想知道该程序的逻辑是怎么样的。如果大家嫌长,我也没办法。谢谢了。说过了,来者有分
update 表a
set
表a的需要更新的列=
(select sum(表b的求和的列) from 表B where 表b的条件列1=@输入参数 and 表b的外键列=表a的主键列),
......
太长啦
存储过程里面是一些修改语句
还创建了一个局部临时表和一个游标
楼主想知道什么
我不明白
我眼花啦
是呀,我刚被分在决策支持小组里,我原来是学JAVA和C++的,一下子转过来有点不适应,心有点急。好了,我以后回提些语法的问题,这样吧,这个帖子就到这里吧,他的具体逻辑我已经分析出来了,谢谢大家了,以后希望大家多多指教,希望多些做决策的朋友来我这里。一会就解贴。麻烦大家了,888