浪潮"内部"资料，绝对经典

lookice · 发表于 2008-4-4 21:18

把报表计算SQL语句发给大家，希望大家有收获，需要一定SQL基础和浪潮表结构熟悉，能得到什么就看大家的了。

[此贴子已经被作者于2008-4-5 9:45:26编辑过]

lookice · 发表于 2008-4-4 21:22

go select F_VAL from BBCONF where F_VKEY='FORCE_ZW'go select F_VAL from BBCONF where F_VKEY='ZW_YJZ'go select count(1) from  ZWPZK where ZWPZK_KJND<='2008' and ZWPZK_KJQJ<='03' and ZWPZK_JZF='0' and ZWPZK_WZF='1' and ZWPZK_ZFF='0'go select F_VAL from LSCONF where F_VKEY='ZW_CSNDQJ'go truncate table #bbpznmTmp364go drop table #bbpznmTmp364go create table #bbpznmTmp364 (F_KJND varchar(4) null,F_KJQJ varchar(2) null,F_PZNM varchar(9) null)go create  index #bbpznmTmp364 on #bbpznmTmp364 ( F_KJND,F_KJQJ,F_PZNM ) go insert into #bbpznmTmp364(F_KJND,F_KJQJ,F_PZNM )              select ZWPZK_KJND,ZWPZK_KJQJ,ZWPZK_PZNM FROM ZWPZK where               (ZWPZK_KJND<'2008' or (ZWPZK_KJND='2008'  and ZWPZK_KJQJ<='03'))                and ZWPZK_JZF='0' and ZWPZK_WZF='1' and ZWPZK_ZFF='0' select count(ZWPZFL_KMBH) from ZWPZFL,#bbpznmTmp364 WHERE ZWPZFL_PZNM=#bbpznmTmp364.F_PZNMgo truncate table #bbkmjehz364go drop table #bbkmjehz364go create table #bbkmjehz364 (F_KJND varchar(4)  null, F_KJQJ varchar(2)  null, F_KMBH varchar(30) null, F_JFJE u001  default 0,  F_DFJE u001  default 0,  F_JFSL u001  default 0,  F_DFSL u001  default 0) go create  index #bbkmjehz364 on #bbkmjehz364 ( F_KJND,F_KJQJ,F_KMBH ) go truncate table #bbkmhztmp364go drop table #bbkmhztmp364go create table #bbkmhztmp364 (F_KJND varchar(4)  null, F_KJQJ varchar(2)  null, F_KMBH varchar(30) null, F_JE   u001  default 0,  F_SL   u001  default 0) go create  index #bbkmhztmp364 on #bbkmhztmp364 ( F_KJND,F_KJQJ,F_KMBH ) go insert into #bbkmjehz364(F_KJND,F_KJQJ,F_KMBH,F_JFJE,F_JFSL,F_DFJE,F_DFSL) select #bbpznmTmp364.F_KJND,#bbpznmTmp364.F_KJQJ,ZWPZFL_KMBH, round(sum(round(ZWPZFL_JE,2)),2), round(sum(round(ZWPZFL_SL,4)),4),0,0 from ZWPZFL,#bbpznmTmp364 WHERE ZWPZFL_PZNM=#bbpznmTmp364.F_PZNM and  ZWPZFL_JZFX='1'  group by F_KJND,F_KJQJ,ZWPZFL_KMBH go insert into #bbkmjehz364(F_KJND,F_KJQJ,F_KMBH,F_JFJE,F_JFSL,F_DFJE,F_DFSL) select #bbpznmTmp364.F_KJND,#bbpznmTmp364.F_KJQJ,ZWKMZD_KMBH, round(sum(round(ZWPZFL_JE,2)),2), round(sum(round(ZWPZFL_SL,4)),4),0,0 from ZWKMZD,ZWPZFL,#bbpznmTmp364 WHERE ZWPZFL_PZNM=#bbpznmTmp364.F_PZNM and  ZWPZFL_JZFX='1' AND CHARINDEX(ZWKMZD_KMBH,ZWPZFL_KMBH)=1 and ZWKMZD_MX=0 group by F_KJND,F_KJQJ,ZWKMZD_KMBH go insert into #bbkmhztmp364(F_KJND,F_KJQJ,F_KMBH,F_JE,F_SL) select #bbpznmTmp364.F_KJND,#bbpznmTmp364.F_KJQJ,ZWPZFL_KMBH, round(sum(round(ZWPZFL_JE,2)),2),  round(sum(round(ZWPZFL_SL,4)),4)  from ZWPZFL,#bbpznmTmp364 WHERE ZWPZFL_PZNM=#bbpznmTmp364.F_PZNM  and ZWPZFL_JZFX='2' group by F_KJND,F_KJQJ,ZWPZFL_KMBH go insert into #bbkmhztmp364(F_KJND,F_KJQJ,F_KMBH,F_JE,F_SL) select #bbpznmTmp364.F_KJND,#bbpznmTmp364.F_KJQJ,ZWKMZD_KMBH, round(sum(round(ZWPZFL_JE,2)),2),  round(sum(round(ZWPZFL_SL,4)),4)  from ZWKMZD,ZWPZFL,#bbpznmTmp364 WHERE ZWPZFL_PZNM=#bbpznmTmp364.F_PZNM  and ZWPZFL_JZFX='2' AND CHARINDEX(ZWKMZD_KMBH,ZWPZFL_KMBH)=1 AND ZWKMZD_MX=0 group by F_KJND,F_KJQJ,ZWKMZD_KMBH go update #bbkmjehz364 set  F_DFJE=F_JE,F_DFSL=F_SL  from  #bbkmjehz364,#bbkmhztmp364 where #bbkmjehz364.F_KJND=#bbkmhztmp364.F_KJND  and   #bbkmjehz364.F_KJQJ=#bbkmhztmp364.F_KJQJ  and   #bbkmjehz364.F_KMBH=#bbkmhztmp364.F_KMBH go insert into #bbkmjehz364 (F_KJND,F_KJQJ,F_KMBH,F_DFJE,F_DFSL,F_JFJE,F_JFSL )  select F_KJND,F_KJQJ,F_KMBH,F_JE,F_SL,0,0  from #bbkmhztmp364 where not exists(select 1 from #bbkmjehz364 where #bbkmjehz364.F_KJND=#bbkmhztmp364.F_KJND  and   #bbkmjehz364.F_KJQJ=#bbkmhztmp364.F_KJQJ  and   #bbkmjehz364.F_KMBH=#bbkmhztmp364.F_KMBH)go truncate table #bbkmhztmp364go drop table #bbkmhztmp364go select count(ZWKMZD_KMBH) from ZWKMZD,ZWPZFL,#bbpznmTmp364 WHERE ZWKMZD_KMBH=ZWPZFL_KMBH AND ZWKMZD_HSWB<>'0' AND ZWPZFL_PZNM=#bbpznmTmp364.F_PZNMgo select count(ZWFZYS_KMBH) from ZWFZYS,#bbpznmTmp364 WHERE ZWFZYS_PZNM=#bbpznmTmp364.F_PZNMgo truncate table #bbfzjehz364go drop table #bbfzjehz364go create table #bbfzjehz364 (F_KJND varchar(4)  null,  F_KJQJ varchar(2)  null,  F_KMBH varchar(30) null,  F_BMBH varchar(20) null,  F_DWBH varchar(20) null,  F_ZGBH varchar(20) null,  F_XM01 varchar(20) null,  F_XM02 varchar(20) null,  F_XM03 varchar(20) null,  F_XM04 varchar(20) null,  F_XM05 varchar(20) null,  F_JFJE u001  default 0,  F_DFJE u001  default 0,  F_JFSL u001  default 0,  F_DFSL u001  default 0) go create  index #bbfzjehz364 on #bbfzjehz364 ( F_KJND,F_KJQJ,F_KMBH,F_BMBH,F_DWBH,F_ZGBH,F_XM01 ) go truncate table #bbfzhztmp364go drop table #bbfzhztmp364go create table #bbfzhztmp364 (F_KJND varchar(4)  null,  F_KJQJ varchar(2)  null,  F_KMBH varchar(30) null,  F_BMBH varchar(20) null,  F_DWBH varchar(20) null,  F_ZGBH varchar(20) null,  F_XM01 varchar(20) null,  F_XM02 varchar(20) null,  F_XM03 varchar(20) null,  F_XM04 varchar(20) null,  F_XM05 varchar(20) null,  F_JE u001  default 0,  F_SL u001  default 0) go create  index #bbfzhztmp364 on #bbfzhztmp364 ( F_KJND,F_KJQJ,F_KMBH,F_BMBH,F_DWBH,F_ZGBH,F_XM01 ) go insert into #bbfzjehz364(F_KJND,F_KJQJ,F_KMBH,F_BMBH,F_DWBH,F_ZGBH,F_XM01,F_XM02,F_XM03,F_XM04,F_XM05,F_JFJE,F_JFSL,F_DFJE,F_DFSL) select #bbpznmTmp364.F_KJND,#bbpznmTmp364.F_KJQJ,ZWFZYS_KMBH,ISNULL(ZWFZYS_BMBH,' '),ISNULL(ZWFZYS_DWBH,' '),ISNULL(ZWFZYS_ZGBH,' '),ISNULL(ZWFZYS_XM01,' '),ISNULL(ZWFZYS_XM02,' '),ISNULL(ZWFZYS_XM03,' '),ISNULL(ZWFZYS_XM04,' '),ISNULL(ZWFZYS_XM05,' '),  round(sum(round(ZWFZYS_JE,2)),2),  round(sum(round(ZWFZYS_SL,4)),4),0,0  from ZWFZYS,#bbpznmTmp364 WHERE ZWFZYS_PZNM=#bbpznmTmp364.F_PZNM and  ZWFZYS_JZFX='1' group by F_KJND,F_KJQJ,ZWFZYS_KMBH,isnull(ZWFZYS_BMBH,' '),isnull(ZWFZYS_DWBH,' '),isnull(ZWFZYS_ZGBH,' '),isnull(ZWFZYS_XM01,' '),isnull(ZWFZYS_XM02,' '),isnull(ZWFZYS_XM03,' '),isnull(ZWFZYS_XM04,' '),isnull(ZWFZYS_XM05,' ') go insert into #bbfzhztmp364(F_KJND,F_KJQJ,F_KMBH,F_BMBH,F_DWBH,F_ZGBH,F_XM01,F_XM02,F_XM03,F_XM04,F_XM05,F_JE,F_SL) select #bbpznmTmp364.F_KJND,#bbpznmTmp364.F_KJQJ,ZWFZYS_KMBH,ISNULL(ZWFZYS_BMBH,' '),ISNULL(ZWFZYS_DWBH,' '),ISNULL(ZWFZYS_ZGBH,' '),ISNULL(ZWFZYS_XM01,' '),ISNULL(ZWFZYS_XM02,' '),ISNULL(ZWFZYS_XM03,' '),ISNULL(ZWFZYS_XM04,' '),ISNULL(ZWFZYS_XM05,' '), round(sum(round(ZWFZYS_JE,2)),2),  round(sum(round(ZWFZYS_SL,4)),4)  from ZWFZYS,#bbpznmTmp364 WHERE ZWFZYS_PZNM=#bbpznmTmp364.F_PZNM  and ZWFZYS_JZFX='2' group by F_KJND,F_KJQJ,ZWFZYS_KMBH,ISNULL(ZWFZYS_BMBH,' '),ISNULL(ZWFZYS_DWBH,' '),ISNULL(ZWFZYS_ZGBH,' '),ISNULL(ZWFZYS_XM01,' '),ISNULL(ZWFZYS_XM02,' '),ISNULL(ZWFZYS_XM03,' '),ISNULL(ZWFZYS_XM04,' '),ISNULL(ZWFZYS_XM05,' ') go update #bbfzjehz364 set F_DFJE= F_JE,F_DFSL=F_SL FROM  #bbfzjehz364,#bbfzhztmp364 where #bbfzjehz364.F_KJND=#bbfzhztmp364.F_KJND  and   #bbfzjehz364.F_KJQJ=#bbfzhztmp364.F_KJQJ  and   #bbfzjehz364.F_KMBH=#bbfzhztmp364.F_KMBH  and   #bbfzjehz364.F_BMBH=#bbfzhztmp364.F_BMBH  and   #bbfzjehz364.F_DWBH=#bbfzhztmp364.F_DWBH  and   #bbfzjehz364.F_ZGBH=#bbfzhztmp364.F_ZGBH  and   #bbfzjehz364.F_XM01=#bbfzhztmp364.F_XM01  and   #bbfzjehz364.F_XM02=#bbfzhztmp364.F_XM02  and   #bbfzjehz364.F_XM03=#bbfzhztmp364.F_XM03  and   #bbfzjehz364.F_XM04=#bbfzhztmp364.F_XM04  and   #bbfzjehz364.F_XM05=#bbfzhztmp364.F_XM05 go insert into #bbfzjehz364 (F_KJND,F_KJQJ,F_KMBH,F_BMBH,F_DWBH,F_ZGBH,F_XM01,F_XM02,F_XM03,F_XM04,F_XM05,F_DFJE,F_DFSL,F_JFJE,F_JFSL )  select F_KJND,F_KJQJ,F_KMBH,F_BMBH,F_DWBH,F_ZGBH,F_XM01,F_XM02,F_XM03,F_XM04,F_XM05,F_JE,F_SL,0,0 from #bbfzhztmp364 where not exists(select 1 from #bbfzjehz364 where #bbfzjehz364.F_KJND=#bbfzhztmp364.F_KJND  and   #bbfzjehz364.F_KJQJ=#bbfzhztmp364.F_KJQJ  and   #bbfzjehz364.F_KMBH=#bbfzhztmp364.F_KMBH  and   #bbfzjehz364.F_BMBH=#bbfzhztmp364.F_BMBH  and   #bbfzjehz364.F_DWBH=#bbfzhztmp364.F_DWBH  and   #bbfzjehz364.F_ZGBH=#bbfzhztmp364.F_ZGBH  and   #bbfzjehz364.F_XM01=#bbfzhztmp364.F_XM01  and   #bbfzjehz364.F_XM02=#bbfzhztmp364.F_XM02  and   #bbfzjehz364.F_XM03=#bbfzhztmp364.F_XM03  and   #bbfzjehz364.F_XM04=#bbfzhztmp364.F_XM04  and   #bbfzjehz364.F_XM05=#bbfzhztmp364.F_XM05)go truncate table #bbfzhztmp364go drop table #bbfzhztmp364go select count(ZWFZYS_KMBH) from ZWFZYS,ZWKMZD,#bbpznmTmp364 WHERE ZWKMZD_KMBH=ZWFZYS_KMBH AND ZWKMZD_HSWB<>'0' AND ZWFZYS_PZNM=#bbpznmTmp364.F_PZNMgo truncate table ZWKMJEBB364go insert into ZWKMJEBB364 select * from ZWKMJE where ZWKMJE_KJND<='2008'go truncate table ZWKMSLBB364go insert into ZWKMSLBB364 select * from ZWKMSL where ZWKMSL_KJND<='2008'go truncate table ZWKMWBBB364go insert into ZWKMWBBB364 select * from ZWKMWB where ZWKMWB_KJND<='2008'go truncate table ZWFZJEBB364go insert into ZWFZJEBB364 select * from ZWFZJE where ZWFZJE_KJND<='2008'go truncate table ZWFZSLBB364go insert into ZWFZSLBB364 select * from ZWFZSL where ZWFZSL_KJND<='2008'go truncate table ZWFZWBBB364go insert into ZWFZWBBB364 select * from ZWFZWB where ZWFZWB_KJND<='2008'go select F_KJND,F_KJQJ from #bbpznmTmp364 group by F_KJND,F_KJQJ order by F_KJND,F_KJQJ go UPDATE ZWKMJEBB364 SET  ZWKMJE_DQYE=round(round(ZWKMJE_DQYE,2)                +round(#bbkmjehz364.F_JFJE,2)                -round(#bbkmjehz364.F_DFJE,2),2)  FROM ZWKMJEBB364,#bbkmjehz364 WHERE ZWKMJE_KJND='2007' AND ZWKMJE_KJQJ>='06' AND F_KJND='2007' AND F_KJQJ='06' AND ZWKMJE_KMBH=#bbkmjehz364.F_KMBH  AND ZWKMJE_KMBH IN(SELECT ZWKMZD_KMBH FROM ZWKMZD WHERE ZWKMZD_XZ='0') go UPDATE ZWKMJEBB364 SET  ZWKMJE_NCYE= round(round(ZWKMJE_NCYE,2)                    +round(#bbkmjehz364.F_JFJE,2)                    -round(#bbkmjehz364.F_DFJE,2),2),  ZWKMJE_DQYE=round(round(ZWKMJE_DQYE,2)                    +round(#bbkmjehz364.F_JFJE,2)                    -round(#bbkmjehz364.F_DFJE,2),2)  FROM ZWKMJEBB364,#bbkmjehz364 WHERE ZWKMJE_KJND='2008'  AND F_KJND='2007' AND F_KJQJ='06' AND ZWKMJE_KMBH=#bbkmjehz364.F_KMBH  AND ZWKMJE_KMBH IN(SELECT ZWKMZD_KMBH FROM ZWKMZD WHERE ZWKMZD_XZ='0') go UPDATE ZWKMJEBB364 SET  ZWKMJE_JFLJ=round(round(ZWKMJE_JFLJ,2)+round(#bbkmjehz364.F_JFJE,2),2), ZWKMJE_DFLJ=round(round(ZWKMJE_DFLJ,2)+round(#bbkmjehz364.F_DFJE,2),2) FROM ZWKMJEBB364,#bbkmjehz364 WHERE ZWKMJE_KJND='2007' AND ZWKMJE_KJQJ>='06' AND F_KJND='2007' AND F_KJQJ='06'  AND ZWKMJE_KMBH=#bbkmjehz364.F_KMBH go UPDATE ZWKMJEBB364 SET  ZWKMJE_JFFS=round(round(ZWKMJE_JFFS,2)+round(#bbkmjehz364.F_JFJE,2),2), ZWKMJE_DFFS=round(round(ZWKMJE_DFFS,2)+round(#bbkmjehz364.F_DFJE,2),2) FROM ZWKMJEBB364,#bbkmjehz364 WHERE ZWKMJE_KJND='2007' AND ZWKMJE_KJQJ='06' AND F_KJND='2007' AND F_KJQJ='06'  AND ZWKMJE_KMBH=#bbkmjehz364.F_KMBH go UPDATE ZWKMSLBB364 SET  ZWKMSL_DQYE=round(round(ZWKMSL_DQYE,4)+round(#bbkmjehz364.F_JFSL,4)-round(#bbkmjehz364.F_DFSL,4),4)  FROM ZWKMSLBB364,#bbkmjehz364 WHERE ZWKMSL_KJND='2007' AND ZWKMSL_KJQJ>='06' and F_KJND='2007' and F_KJQJ='06'  AND ZWKMSL_KMBH=#bbkmjehz364.F_KMBH  AND ZWKMSL_KMBH IN(SELECT ZWKMZD_KMBH FROM ZWKMZD WHERE ZWKMZD_XZ='0') go UPDATE ZWKMSLBB364 SET  ZWKMSL_NCYE=round(round(ZWKMSL_NCYE,4)+round(#bbkmjehz364.F_JFSL,4)-round(#bbkmjehz364.F_DFSL,4),4),  ZWKMSL_DQYE=round(round(ZWKMSL_DQYE,4)+round(#bbkmjehz364.F_JFSL,4)-round(#bbkmjehz364.F_DFSL,4),4)  FROM ZWKMSLBB364,#bbkmjehz364 WHERE ZWKMSL_KJND='2008' and F_KJND='2007' and F_KJQJ='06'  AND ZWKMSL_KMBH=#bbkmjehz364.F_KMBH  AND ZWKMSL_KMBH IN(SELECT ZWKMZD_KMBH FROM ZWKMZD WHERE ZWKMZD_XZ='0') go UPDATE ZWKMSLBB364 SET  ZWKMSL_JFLJ=round(round(ZWKMSL_JFLJ,4)+round(#bbkmjehz364.F_JFSL,4),4), ZWKMSL_DFLJ=round(round(ZWKMSL_DFLJ,4)+round(#bbkmjehz364.F_DFSL,4),4) FROM ZWKMSLBB364,#bbkmjehz364 WHERE ZWKMSL_KJND='2007' AND ZWKMSL_KJQJ>='06' and F_KJND='2007' and F_KJQJ='06'  AND ZWKMSL_KMBH=#bbkmjehz364.F_KMBH go UPDATE ZWKMSLBB364 SET  ZWKMSL_JFFS=round(round(ZWKMSL_JFFS,4)+round(#bbkmjehz364.F_JFSL,4),4), ZWKMSL_DFFS=round(round(ZWKMSL_DFFS,4)+round(#bbkmjehz364.F_DFSL,4),4) FROM ZWKMSLBB364,#bbkmjehz364 WHERE ZWKMSL_KJND='2007' AND ZWKMSL_KJQJ='06' and F_KJND='2007' and F_KJQJ='06'  AND ZWKMSL_KMBH=#bbkmjehz364.F_KMBH

[此贴子已经被作者于2008-4-5 9:47:16编辑过]

lookice · 发表于 2008-4-4 21:31

希望大家多把精力放在报表上，以EXCEL为平台定义函数从财务软件取数，满足大量报表对财务软件数据的需要。上面代码考虑到包含未记账凭证，因为zwkmjebb364这个表，当你重新计算一次整张报表的时候会发生改变，select top 1 name from sysobjects where name like 'zwkmjebb%' and len (name)=(len('zwkmjebb')+3) order by right(name,3)  desc将结果赋值给你的VBA中查询表名称，希望大家对代码多改进。

XrnPjCuc.rar (4.96 KB, 下载次数: 101)

[此贴子已经被作者于2008-4-5 9:51:54编辑过]

yunli · 发表于 2008-5-28 13:18

希望大家多把精力放在报表上，以EXCEL为平台定义函数从财务软件取数，满足大量报表对财务软件数据的需要。

秦地罗敷 · 发表于 2008-12-5 22:14

ak618 · 发表于 2010-4-18 11:05

你的文件怎么用

480000118682 · 发表于 2010-7-31 22:18

		自动登录	找回密码
密码			免费注册

[分享] 浪潮"内部"资料，绝对经典

浪潮"内部"资料，绝对经典2

不贴了发上附件供大家研究

？

你的文件怎么用

评分