数据分层汇总交叉报表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 _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
骨肿瘤、脊柱外科二病区