数据分层汇总交叉报表SQL语句实现方法.docVIP

数据分层汇总交叉报表SQL语句实现方法在管理系统中，管理人员往往需要对业务数据进行不同需求的分层汇总，并产生各种形式交叉报表。为了实现此类报表，程序员需要构造层次结构非常复杂的SQL语句，甚至使用前台编程工具或其它报表工具来完成。以下通过二个实例，介绍此类报表的实现方法。一、WITH as 语句使用WITHAS语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到一定的优化作用。with子句是9i新增语法。你可以在任何一个顶层的SELECT语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。with子句中不能嵌套定义，但子查询中出现的“子查询定义”语句可以引用已定义的子查询名称。复杂的查询会产生很大的sql，with as语法显示一个个中间结果，显得有条理些，可读性与易维护性大为提高。前面的中间结果可以被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时可以提升效率。语法结构：with t1 as (.),t2 as (.) 二、字典准备 为了实现数据分层汇总交叉报表，需要建立行的层次结构表与列的交叉汇总对照表。1、 交叉汇总对照表：一般分为，代码字段与代码汇总二个字段。如：SELECT fee_code,fee_stat_cate FROM FIN_COM_FEECODESTAT WHERE REPORT_CODE = ZY11FEE_CIDE为数据表中的费用代码，FEE_STAT_CATE为费用汇总归类代码。2、层次结构表：一般分为，需要分层汇总的代码、汇总报表行标题、汇总报表排序代码、层次代码、分层汇总的父节点代码与报表显示标志。如：某报表按科室汇总产生分层报表的层次结构表如下： DEPT_CODEDEPT_NAMESORTIDROOTLEVELLEVEL_DEPTSHOW_F1S001本月合计012S10012S005内科12S001130014 消化内科113S005140015 血液内科123S005150011 内科心血管133S005160013 内分泌科143S005170016 呼吸内科153S005180017 肾病内科163S00519S006综合科22S0011100033 综合一213S0061110034 综合二223S0061120035 综合三233S0061130036 综合四243S0061140048 综合五253S0061150037 综合六263S0061160019感染科32S0011170018神经内科42S005118S002外科52S001119S007 普外科513S0021200042 肝胆胰外科一病区51014S0071210002 肝胆胰外科二病区51024S0071220001 甲乳疝血管外科病区51034S0071230003 结直肠肛门外科病区51044S0071240004 胸外科523S0021250006 泌尿外科52023S0021260005 脑外科52033S002127S003 骨科533S0021280009 创伤修复病区53014S0031290008 显微、手外科病区53024S0031300040 脊柱外科一病区53034S0031310044 骨肿瘤、脊柱外科二病区53044S0031320045 关节外科病区53054S0031330046 创伤骨科病区53064S003134S015 肝胆研究院543S0021350054 肝胆研究院一574S0151360079 肝胆研究院二584S015137S016儿科62S0011380038 儿科613S0161390039 新生儿623S016140S008妇产科72S0011410027 妇科713S0081420029 产科723S0081430024耳鼻喉科82S0171440025眼科92S0171450032皮肤科A2S0011460026口腔科A02S0171SQL语句: select * from COM_DEPT_FATHERDept_code 科室代码 dept_bane 科室结构名称 Sortid科室结构排序 Rootlevel 科室层次Level_dept 父节点代码 SHOW_F报表显示列三、实现方法1、业务数据如：原始数据表：FIN_IPB_FEEINFO INHOS_DEPTCODEFEE_CODETOT_COSTBALANCE_DATE1001305547.002012-12-29 08:54:11200136198.002012-12-29 08:54:1130013181240.002012-12-29 08:54:11400130539.502012-12-29 08:54:115001318170.002012-12-29 08:54:11600130539.502012-12-29 08:54:1170013141123.002012-12-29 08:54:118001388872.502012-12-29 08:54:1190013001106.852012-12-29 08:54:1110001317135.002012-12-29 08:54:11110013171487.002012-12-29 08:54:111200130018.972012-12-29 08:54:111300136208.002012-12-29 08:54:111400135318.002012-12-29 08:54:1115001305547.002012-12-29 08:54:111600136198.002012-12-29 08:54:1117001317130.002012-12-29 08:54:1118001317131.002012-12-29 08:54:11190013221550.002012-12-29 08:54:11200013002-100.502012-12-29 08:54:11210013001-8.972012-12-29 08:54:1122001317115.002012-12-29 08:54:112300015318.002012-12-29 08:54:1124000105532.002012-12-29 08:54:112500016204.002012-12-29 08:54:112600016198.002012-12-29 08:54:11字段名称INHOS_DEPTCODE 科室代码、FEE_CODE费用代码、TOT_COST金额、BALANCE_DATE统计日期2、生成交叉表 利用交叉汇总对照表，生成各底层统计单位的交叉表。SQL语句如下：select a.INHOS_DEPTCODE, count(distinct a.inpatient_no) as 人次, sum(a.tot_cost) as 合计收入, sum(decode(c.fee_stat_cate, 01, a.tot_cost, 0) as 药品收入, sum(decode(c.fee_stat_cate, 08, a.tot_cost, 0) as 床位收入, sum(decode(c.fee_stat_cate, 02, a.tot_cost, 0) as 检查收入, sum(decode(c.fee_stat_cate, 04, a.tot_cost, 0) as 治疗收入, sum(decode(c.fee_stat_cate, 05, a.tot_cost, 0) as 手术收入, sum(decode(c.fee_stat_cate, 03, a.tot_cost, 0) as 化验收入, sum(decode(c.fee_stat_cate, 06, a.tot_cost, 0) as 卫生材料, sum(decode(c.fee_stat_cate, 09, a.tot_cost, 0) as 诊察收入, sum(decode(c.fee_stat_cate, 10, a.tot_cost, 0) as 护理收入, sum(decode(c.fee_stat_cate, 07, a.tot_cost, 0) as 其他收入 from FIN_IPB_FEEINFO a, (SELECT * FROM FIN_COM_FEECODESTAT WHERE REPORT_CODE = ZY11) c where a.balance_state = 1 AND a.FEE_CODE = c.FEE_CODE and a.balance_date = TO_DATE(2013-08-01 00:00:00,yyyy-mm-dd hh24:mi:ss) AND a.balance_date = TO_DATE(2013-07-01 00:00:00,yyyy-mm-dd hh24:mi:ss) AND a.balance_date = to_date(2013-05-01 00:00:00, YYYY-MM-DD HH24:MI:SS) AND A.REG_DATE = to_date(2013-05-31 23:59:59, YYYY-MM-DD HH24:MI:SS) AND A.VALID_FLAG = 1 GROUP BY C.CODE ORDER BY C.CODE) A1 left join (SELECT * FROM COM_DICTIONARY WHERE TYPE = ZNSR and NAME门诊补号) A2 on (A1.DEPT_CODE=A2.CODE) WHERE A2.CODE NOT IN (0058,0125) Order by A2.CODE) select * from DEPT_TATDEPT_CODECOMMONEXPERTEMERGENCYREG_ALLDEPT_NAME000711320115门诊外科00510101VIP特诊005911200112本院保健科006045454723136905内科门诊006142982019846302外科门诊0062537225381148024妇产科门诊0064613112421597532眼科门诊00651427101428保健科00665653021868放化疗门诊00675141710685康复科门诊006817801233273040神经科门诊0069725185832586中医科门诊0070522141911942肿瘤科门诊007177100771高干门诊007214000140PICC专科门诊00741489538132040感染科0075601061同位素门诊0078380038造口伤口门诊00946544806967446口腔科0095639213132767981耳鼻喉科00971512980449门诊妇瘤0098684819942009042皮肤科0099226713528544473儿科01246634006634方便门诊(1楼)01381741850359肝胆外科门诊0141880088体检中心019930910310疼痛门诊023917960113检验科门诊025891681601732生殖医学中心02701787104602833门诊普外02712351123423587门诊骨外02721090110422196门诊泌外0273150791230门诊脑外0274159950254门诊胸外0275207499033067门诊心血管02761649192813578门诊消化02771602107902681门诊呼吸02782057128913347门诊内分泌02796222720894门诊血液028081952101340门诊肾病0281109019放射学专科门诊028222220224特需诊疗中心4、 年度记录-本年度合计SELECT 0000 as 序号,本年到本月累计 as 科室名称,SUM( DECODE(B.MARK,01,1,02,1,03,1,04,1,05,1,06,1,07,1,08,1,09,1,14,1,15,1,16,1,17,1,0) as 总计, -不统计急诊06 SUM( DECODE(B.MARK,01,1,02,1,03,1,04,1,05,1,07,1,08,1,09,1,14,1,15,1,16,1,17,1,0) as 小计, SUM( DECODE(B.MARK,04,1,05,1,09,1,16,1,17,1,10,-1,11,-1,12,-1,13,-1,0) ) AS 普诊, SUM( DECODE(B.MARK,01,1,02,1,03,1,07,1,08,1,10,1,11,1,12,1,13,1,14,1,15,1,0) ) AS 专家, SUM( DECODE(B.MARK,06,1,0) ) AS 急诊 FROM FIN_OPR_REGISTER AA, 挂号类型 B WHERE B.CODE = AA.REGLEVL_CODE AND AA.REG_DATE = trunc(to_date(2013-01-01 00:00:00, YYYY-MM-DD HH24:MI:SS),yyyy) AND AA.REG_DATE = to_date(2013-05-31 23:59:59, YYYY-MM-DD HH24:MI:SS) AND AA.VALID_FLAG =15、 本期累计select 0001 as 序号,本月合计 as 科室名称,sum(REG_ALL) as 总计,sum(COMMON+EXPERT) as 小计,sum(COMMON) as 普诊,sum(EXPERT) as 专家, sum(EMERGENCY) as 急诊 from DEPT_TAT6、 完整SQL语句with -挂号科室汇总 dept_tat as (SELECT A2.Code AS DEPT_CODE, NVL(A1.common,0) AS common, NVL(A1.expert,0) AS