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

数据分层汇总交叉报表SQL语句实现方法 在管理系统中，管理人员往往需要对业务数据进行不同需求的分层汇总，并产生各种形式交叉报表。为了实现此类报表，程序员需要构造层次结构非常复杂的SQL语句，甚至使用前台编程工具或其它报表工具来完成。 以下通过二个实例，介绍此类报表的实现方法。 一、WITH as 语句 使用WITH?AS?语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle?数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到一定的优化作用。with子句是9i新增语法。你可以在任何一个顶层的SELECT?语句以及几乎所有类型的子查询语句前，使用子查询定义子句。被定义的子查询名称可以在主查询语句以及所有的子查询语句中引用，但未定义前不能引用。with子句中不能嵌套定义也就是with子句中不能有with子句，但子查询中出现的“子查询定义”语句可以引用已定义的子查询名称。可以引用前面已经定义的with子句?。 复杂的查询会产生很大的sql，with as语法显示一个个中间结果，显得有条理些，可读性与易维护性大为提高。前面的中间结果可以被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时可以提升效率。 语法结构： with t1 as (...),t2 as (..) 二、字典准备 为了实现数据分层汇总交叉报表，需要建立行的层次结构表与列的交叉汇总对照表。 交叉汇总对照表：一般分为，代码字段与代码汇总二个字段。 如：SELECT fee_code,fee_stat_cate FROM FIN_COM_FEECODESTAT WHERE REPORT_CODE = ZY11 FEE_CIDE为数据表中的费用代码，FEE_STAT_CATE为费用汇总归类代码。 2、层次结构表：一般分为，需要分层汇总的代码、汇总报表行标题、汇总报表排序代码、层次代码、分层汇总的父节点代码与报表显示标志。 如：某报表按科室汇总产生分层报表的层次结构表如下： DEPT_CODE DEPT_NAME SORTID ROOTLEVEL LEVEL_DEPT SHOW_F 1 S001 本月合计 01 2 S100 1 2 S005 内科 1 2 S001 1 3 0014 消化内科 11 3 S005 1 4 0015 血液内科 12 3 S005 1 5 0011 内科心血管 13 3 S005 1 6 0013 内分泌科 14 3 S005 1 7 0016 呼吸内科 15 3 S005 1 8 0017 肾病内科 16 3 S005 1 9 S006 综合科 2 2 S001 1 10 0033 综合一 21 3 S006 1 11 0034 综合二 22 3 S006 1 12 0035 综合三 23 3 S006 1 13 0036 综合四 24 3 S006 1 14 0048 综合五 25 3 S006 1 15 0037 综合六 26 3 S006 1 16 0019 感染科 3 2 S001 1 17 0018 神经内科 4 2 S005 1 18 S002 外科 5 2 S001 1 19 S007 普外科 51 3 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 胸外科 52 3 S002 1 25 0006 泌尿外科 5202 3 S002 1 26 0005 脑外科 5203 3 S002 1 27 S003 骨科 53 3 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 肝胆研究院 54 3 S002 1 35 0054 肝胆研究院一 57 4 S015 1 36 0079