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

数据分层汇总交叉报表 SQL 语句实现方法在管理系统中, 管理人员往往需要对业务数据进行不同需求的分层汇总, 并产生各种形式交叉报表。为了实现此类报表, 程序员需要构造层次结构非常复杂的 SQL 语句, 甚至使用前台编程工具或其它报表工具来完成。以下通过二个实例,介绍此类报表的实现方法。一、 WITH as 语句使用 WITH AS 语句可以为一个子查询语句块定义一个名称, 使用这个子查询名称可以在查询语句的很多地方引用这个子查询。 Oracle 数据库像对待内联视图或临时表一样对待被引用的子查询名称,从而起到一定的优化作用。 with 子句是 9i 新增语法。你可以在任何一个顶层的 SELECT 语句以及几乎所有类型的子查询语句前, 使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用,但未定义前不能引用。 with 子句中不能嵌套定义<也就是 with 子句中不能有 with 子句> ,但子查询中出现的“子查询定义”语句可以引用已定义的子查询名称。< 可以引用前面已经定义的 with 子句>。复杂的查询会产生很大的 sql , with as 语法显示一个个中间结果,显得有条理些,可读性与易维护性大为提高。前面的中间结果可以被语句中的 select 或后面的中间结果表引用,类似于一个范围仅限于本语句的临时表,在需要多次查询某中间结果时可以提升效率。语法结构: with t1as ( ... ),t2 as ( ..) 二、字典准备为了实现数据分层汇总交叉报表,需要建立行的层次结构表与列的交叉汇总对照表。 1、交叉汇总对照表:一般分为,代码字段与代码汇总二个字段。如: SELECT fee_code,fee_stat_cate FROM _FEECODESTAT WHERE REPORT_CODE = 'ZY11' FEE_CIDE 为数据表中的费用代码, FEE_STAT_CATE 为费用汇总归类代码。 2 、层次结构表:一般分为,需要分层汇总的代码、汇总报表行标题、汇总报表排序代码、层次代码、分层汇总的父节点代码与报表显示标志。如:某报表按科室汇总产生分层报表的层次结构表如下: DEPT_CODE DEPT_NAME SORTID ROOTLEVEL LEVEL_DEPT SHOW_F 1 S001 本月合计 012 S100 1 2 S005 内科 12 S001 1 3 0014 消化内科 113 S005 1 4 0015 血液内科 123 S005 1 5 0011 内科心血管 133 S005 1 6 0013 内分泌科 143 S005 1 7 0016 呼吸内科 153 S005 1 8 0017 肾病内科 163 S005 1 9 S006 综合科 22 S001 1 10 0033 综合一 213 S006 1 11 0034 综合二 223 S006 1 12 0035 综合三 233 S006 1 13 0036 综合四 243 S006 1 14 0048 综合五 253 S006 1 15 0037 综合六 263 S006 1 16 0019 感染科 32 S001 1 17 0018 神经内科 42 S005 1 18 S002 外科 52 S001 1 19 S007 普外科 513 S002 1 20 0042 肝胆胰外科一病区 5101 4 S007 1 21 0002 肝胆胰外科二病区 5102 4 S007 1 22 0001 甲乳疝血管外科病区 5103 4 S007 1 23 0003 结直肠肛门外科病区 5104 4 S007 1 24 0004 胸外科 523 S002 1 25 0006 泌尿外科 5202 3 S002 1 26 0005 脑外科 5203 3 S002 1 27 S003 骨科 533 S002 1 28 0009 创伤修复病区 5301 4 S003 1 29 0008 显微、手外科病区 5302 4 S003 1 30 0040 脊柱外科一病区 5303 4 S003 1 31 0044 骨肿瘤、脊柱外科二病区 5304 4 S003 1 32 0045 关节外科病区 5305 4 S003 1 33 0046 创伤骨科病区 5306 4 S003 1 34 S015 肝胆研究院 543 S002 1 35 0054 肝胆研究院一 574 S015 1 36 0079 肝胆研究院二 584 S015 1 37 S016 儿科 62 S001 1 38 0038 儿科 613 S016 1 39 0039 新生儿 623 S016 1 40 S008 妇产科 72 S001 1 41 0027 妇科 713 S008 1