- 用户操作
- [留言] [发消息] [加为好友]
- 订阅我的博客
-
-
-
-
- wai631的公告
- 君不见黄河之水天上来， 奔流到海不复回。 君不见高堂明镜悲白发， 朝如青丝暮成雪。 人生得意须尽欢， 莫使金樽空对月。 天生我材必有用， 千金散尽还复来。 烹羊宰牛且为乐， 会须一饮三百杯。 岑夫子，丹丘生， 将进酒，杯莫停。 与君歌一曲， 请君为我侧耳听。 钟鼓馔玉不足贵， 但愿长醉不复醒。 古来圣贤皆寂寞， 惟有饮者留其名。 陈王昔时宴平乐， 斗酒十千恣欢谑。 主人何为言少钱， 径须沽取对君酌。 五花马，千金裘， 呼儿将出换美酒， 与尔同销万古愁。
- 文章分类
- 常用网站
- CSS中文手册
- erp100
- PostgreSQL 7.1.1 Documentation
- SQL2K手册
- 网页淘吧
- 专家博客
- 【孟子E章】
- 刘彦博
- 张孝强博客
- 存档
期初开帐反算材料和多层制成
收藏
select * from tc_ecr_file
select * from tc_ecr_file
select * from ecu_file
select * from ecb_file
truncate table t_duble_ecm
select distinct t01_ta_ecm03,t01_ecm03par,t01_ecm01,ta_ecb01 from t01_ecm,ecb_file
where t01_ecm03par=ecb01 and ta_ecb02=t01_ta_ecm03 and ta_ecb02<>ta_ecb01 and t01_ecm01='5101-080700040'
select distinct ecm03_par,ta_ecm03
from ecm_file,sfb_file
where ecm301>0 and sfb01=ecm01 and sfb87='Y'
group by ta_ecm03,ecm03_par;
--资料准备
create table t_duble_ecm as
select distinct ecm03_par,ta_ecm03,'1' as sou ,ta_ecm03 as s_taecm03,ecm11
from ecm_file,sfb_file
where ecm301>0 and sfb01=ecm01 and sfb87='Y'
drop table t_duble_ecm
truncate table t_duble_ecm
insert into t_duble_ecm (ecm03_par,ta_ecm03,sou,s_taecm03,ecm11 )
select distinct ecm03_par,ta_ecm03,'1' ,ta_ecm03
from ecm_file,sfb_file
where ecm301>0 and sfb01=ecm01 and sfb87='Y' --and ecm03_par='AA002002700'
group by ta_ecm03,ecm03_par;
commit
-- t_duble_ecm 多层展开
insert into t_duble_ecm (ecm03_par,ta_ecm03,sou,s_taecm03,ecm11 )
--select ecm03_par,ta_ecb01,'2',s_taecm03,ecb03,ta_ecb01,ta_ecb02 from t_duble_ecm,ecb_file
--where ecm03_par=ecb01 and ta_ecb02=ta_ecm03 and ta_ecb02<>ta_ecb01 and sou='1'
select ecm03_par,ta_ecu01,'2',s_taecm03,ecu02 --,ta_ecu01,ta_ecu02
from t_duble_ecm,ecu_file
where ecm03_par=ecu01 and ta_ecu02=ta_ecm03
and sou='1' and ecm11=ecu02
insert into t_duble_ecm (ecm03_par,ta_ecm03,sou,s_taecm03,ecm11 )
select ecm03_par,ta_ecu01,'3',s_taecm03,ecu02 --,ta_ecu01,ta_ecu02
from t_duble_ecm,ecu_file
where ecm03_par=ecu01 and ta_ecu02=ta_ecm03
and sou='2' and ecm11=ecu02
commit;
truncate table t02_ecm_ecr02;
truncate table t01_ecm
insert into t01_ecm
select ecm01,a.ta_ecm03,ecm03,b.ecm11,b.ecm03_par,ecm301,ta_ecm05/ta_ecm04,sou--,a.S_TAECM03
from ecm_file b,sfb_file,t_duble_ecm a
where a.ecm03_par=b.ecm03_par and b.ta_ecm03= a.s_taecm03
and ecm301>0 and sfb01=ecm01 and sfb87='Y' and sou='1'--and a.ta_ecm03<> b.ta_ecm03;
commit;
--资料计算
declare
li_start number(8):=0;
ls_ecm01 varchar(16);
ld_ecm02 number(5);
ld_ecm03 number(5):=0;
ls_ecm03par varchar(40);
ls_taecm03 varchar(6);
ls_ecm11 varchar(6);
ld_qpa number(16,8);
ld_ecm301 number(15,3);
ls_ecr04 varchar(10) ;
ls_ecr05 varchar(40);
ld_ecr06 number(16,8);
ld_ecr07 number(16,8);
l_bmz02 varchar(20);
LD_SFA02 number(5) ;
ld_count number(5);
ld_ecm909 number(6,3);
ld_ecm908 number(6,3);
cursor cur_img is
select t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa
from t01_ecm ;--where sou='1';--where t01_ecm01='5101-080700009';
begin
open cur_img;
loop
fetch cur_img into ls_ecm01,ls_taecm03,ld_ecm03,ls_ecm11,ls_ecm03par,ld_ecm301,ld_qpa;
exit when cur_img%notfound;
ld_count:=0 ;
select count(*) into ld_count from tc_ecr_file
where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11
and tc_ecr03=ls_taecm03 and tc_ecr04<=ld_ecm03;
if ld_count>0 then
insert into t02_ecm_ecr02(t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa,t02_ecm03par,qpa2)
select ls_ecm01,tc_ecr03,tc_ecr04,tc_ecr02,tc_ecr01,ld_ecm301,ld_qpa,tc_ecr05,tc_ecr06/tc_ecr07
from tc_ecr_file
where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11
and tc_ecr03=ls_taecm03 and tc_ecr04<=ld_ecm03;
end if;
end loop ;
close cur_img ;
commit;
end ;
alter table t01_ecm add ( sou varchar(6));
truncate table t01_ecm ;
insert into t01_ecm
select ecm01,a.ta_ecm03,ecm03,ecm11,b.ecm03_par,ecm301,ta_ecm05/ta_ecm04,sou--,a.S_TAECM03
from ecm_file b,sfb_file,t_duble_ecm a
where a.ecm03_par=b.ecm03_par and b.ta_ecm03= a.s_taecm03
and ecm301>0 and sfb01=ecm01 and sfb87='Y' and sou='2'--and a.ta_ecm03<> b.ta_ecm03;
commit;
select count(*)
--t02_ecm_ecr02.*,sfa03,t01_ecm301*qpa*qpa2,sfa05
from t02_ecm_ecr02,sfa_file
where sfa01=t01_ecm01
and t02_ecm03par=sfa03
select count(*)
from t02_ecm_ecr02
group by t01_ecm01,t02_ecm03par
--,ecm_file
where t01_ecm01=ecm01 and t01_ta_ecm03<>ta_ecm03 ecm03_par;
--资料计算
declare
li_start number(8):=0;
ls_ecm01 varchar(16);
ld_ecm02 number(5);
ld_ecm03 number(5):=0;
ls_ecm03par varchar(40);
ls_taecm03 varchar(6);
ls_ecm11 varchar(6);
ld_qpa number(16,8);
ld_ecm301 number(15,3);
ls_ecr04 varchar(10) ;
ls_ecr05 varchar(40);
ld_ecr06 number(16,8);
ld_ecr07 number(16,8);
l_bmz02 varchar(20);
LD_SFA02 number(5) ;
ld_count number(5);
ld_ecm909 number(6,3);
ld_ecm908 number(6,3);
cursor cur_img is
select t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa
from t01_ecm ;--where sou<>'1';--where t01_ecm01='5101-080700009';
begin
open cur_img;
loop
fetch cur_img into ls_ecm01,ls_taecm03,ld_ecm03,ls_ecm11,ls_ecm03par,ld_ecm301,ld_qpa;
exit when cur_img%notfound;
ld_count:=0 ;
select count(*) into ld_count from tc_ecr_file
where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11
and tc_ecr03=ls_taecm03 and tc_ecr04<=ld_ecm03;
if ld_count>0 then
insert into t02_ecm_ecr02(t01_ecm01,t01_ta_ecm03,t01_ecm03,t01_ecm11,t01_ecm03par,t01_ecm301,qpa,t02_ecm03par,qpa2)
select ls_ecm01,tc_ecr03,tc_ecr04,tc_ecr02,tc_ecr01,ld_ecm301,ld_qpa,tc_ecr05,tc_ecr06/tc_ecr07
from tc_ecr_file
where tc_ecr01=ls_ecm03par and tc_ecr02=ls_ecm11
and tc_ecr03=ls_taecm03 and tc_ecr04<=ld_ecm03;
end if;
end loop ;
close cur_img ;
commit;
end ;
select * from ecm_tmp1
where trim(ecm03_par) not in
(select sfb05 from sfb_file )
发表于 @ 2008年11月20日 23:16:00 | 评论( loading... ) | 举报| 收藏
旧一篇:都是JOIN ON惹的祸 | 新一篇:sql 中的除0零运算
- 查看最新精华文章 请访问博客首页相关文章
-
- 发表评论
-
- 表 情：
-
- 评论内容：
- 用 户 名：