济南建站方案,长沙最好玩的地方排名,网站开发宣传标语,uniapp小程序开发教程区域平台统计报表#xff0c;省--市--区 汇总#xff0c;还有各级医院#xff0c;汇总与列表要在一个列表显示。用到ORACLE 会话时临时表 GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS;递归树#xff1a; START WITH P.PARENTORG ‘ROOT‘CONNECT BY PRIOR P.ORG…区域平台统计报表省--市--区 汇总还有各级医院汇总与列表要在一个列表显示。用到ORACLE 会话时临时表 GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS;递归树 START WITH P.PARENTORG ‘ROOT‘CONNECT BY PRIOR P.ORGCODE P.PARENTORG;WITH 连续嵌套记录一下便于查阅。CREATE OR REPLACE PACKAGE BODY PKG_JXKH_SHXBB ASPROCEDURE MJZGH(P_REPORTID IN VARCHAR2,P_UNITID IN VARCHAR2,--要查询的行政区划P_SDATE IN VARCHAR2,--要查询的开始日期P_EDATE IN VARCHAR2,--要查询的开始日期V_CUR OUT REFCURSORTYPE) ISPRAGMA AUTONOMOUS_TRANSACTION;P_sql VARCHAR2(4000);BEGINP_sql : ‘CREATE GLOBAL TEMPORARY TABLE TMP_MZJZGH (PARENTCODE VARCHAR2(60),PARENTUNITID VARCHAR2(60),PARENTUNITNAME VARCHAR2(60),A NUMBER,B NUMBER,C NUMBER) ON COMMIT PRESERVE ROWS‘;--EXECUTE IMMEDIATE P_sql;EXECUTE IMMEDIATE ‘TRUNCATE TABLE TMP_MZJZGH‘;COMMIT;INSERT INTO TMP_MZJZGH(PARENTCODE,PARENTUNITID,PARENTUNITNAME)SELECT P.PARENTORG,P.ORGCODE,P.MANAGERORGNAMEFROM PMR005_ORG PWHERE P.BELONGTO 3AND P.STATUS ‘1‘START WITH P.PARENTORG ‘ROOT‘CONNECT BY PRIOR P.ORGCODE P.PARENTORG;FOR CUR IN (WITH TMP AS (SELECT A.PARENTORG,A.ORGCODE,A.MANAGERORGNAME,TT.*FROM PMR005_ORG A,(SELECT T.UNITID,SUM(CASEWHEN T.MetaDATAID ‘MZ_JZXX_GHRC‘ THENT.MetaDATAVALUEELSE0END) SUMGHRC,SUM(CASEWHEN T.MetaDATAID ‘MZ_FY_JZRC‘ THENT.MetaDATAVALUEELSE0END) SUMJZRC,SUM(CASEWHEN T.MetaDATAID ‘MZ_JZXX_LGRC‘ THENT.MetaDATAVALUEELSE0END) SUMLGRCFROM JXKH_MetaDTAVALUE TWHERE T.MetaDATAID IN(‘MZ_JZXX_GHRC‘,‘MZ_FY_JZRC‘,‘MZ_JZXX_LGRC‘)AND INSTR(P_UNITID,UNITID) 0AND T.STATDATE TO_DATE(P_SDATE,‘YYYY-MM-DD‘)AND T.STATDATE TO_DATE(P_EDATE,‘YYYY-MM-DD‘)GROUP BY T.UNITID) TTWHERE A.ORGCODE TT.UNITIDAND A.BELONGTO 4),TMP2 AS (SELECT P2.PARENTORG,P2.ORGCODE,P2.MANAGERORGNAME,SUM(SUMGHRC) A,SUM(SUMJZRC) B,SUM(SUMLGRC) CFROM PMR005_ORG P2,TMPWHERE P2.PARENTORG (SELECT ORGCODEFROM PMR005_ORGWHERE PARENTORG ‘ROOT‘)AND (P2.ORGCODE TMP.ORGCODE ANDP2.ORGTYPE ‘2‘)GROUP BY P2.PARENTORG,P2.MANAGERORGNAMEUNIONSELECT P3.PARENTORG,P3.ORGCODE,P3.MANAGERORGNAME,SUM(SUMGHRC),SUM(SUMJZRC),SUM(SUMLGRC)FROM TMP,PMR005_ORG P3WHERE (P3.ORGCODE TMP.PARENTORG ORP3.ORGCODE TMP.ORGCODE)AND P3.BELONGTO ‘2‘GROUP BY P3.PARENTORG,P3.MANAGERORGNAME),TMP3 AS (SELECT PARENTORG,ORGCODE,MANAGERORGNAME,A,B,CFROM TMP2UNIONSELECT ‘ROOT‘,‘14000000‘,‘山西省‘,SUM(A),SUM(B),SUM(C)FROM TMP2GROUP BY ‘ROOT‘,‘山西省‘UNIONSELECT P4.PARENTORG,P4.ORGCODE,P4.SHORTNAME,SUM(TP.A),SUM(TP.B),SUM(TP.C)FROM TMP2 TP,PMR005_ORG P4WHERE TP.PARENTORG P4.ORGCODEAND P4.PARENTORG (SELECT ORGCODEFROM PMR005_ORGWHERE PARENTORG ‘ROOT‘AND ORGTYPE ‘1‘)GROUP BY P4.PARENTORG,P4.SHORTNAME)SELECT * FROM TMP3)LOOPUPDATE TMP_MZJZGHSET A CUR.A,B CUR.B,C CUR.CWHERE PARENTCODE CUR.PARENTORGAND PARENTUNITID CUR.ORGCODEAND PARENTUNITNAME CUR.MANAGERORGNAME;END LOOP;COMMIT;OPEN V_CUR FORSELECT A.PARENTCODE,A.PARENTUNITID,A.PARENTUNITNAME,A.A MZ_JZXX_GHRC,A.B MZ_FY_JZRC,A.C MZ_JZXX_LGRCFROM TMP_MZJZGH A;END;END;
