数据分层汇总交叉报表SQL语句实现方式.docx
《数据分层汇总交叉报表SQL语句实现方式.docx》由会员分享，可在线阅读，更多相关《数据分层汇总交叉报表SQL语句实现方式.docx（29页珍藏版）》请在冰豆网上搜索。
数据分层汇总交叉报表SQL语句实现方式
数据分层汇总交叉报表SQL语句实现方式
在治理系统中，治理人员往往需要对业务数据进行不同需求的分层汇总，并产生各类形式交叉报表。
为了实现此类报表，程序员需要构造层次结构超级复杂的SQL语句，乃至利用前台编程工具或其它报表工具来完成。
以下通过二个实例，介绍此类报表的实现方式。
一、WITHas语句
利用WITH AS 语句能够为一个子查询语句块概念一个名称，利用那个子查询名称能够在查询语句的很多地址引用那个子查询。
Oracle 数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到必然的优化作用。
with子句是9i新增语法。
你能够在任何一个顶层的SELECT 语句和几乎所有类型的子查询语句前，利用子查询概念子句。
被概念的子查询名称能够在主查询语句和所有的子查询语句中引用，但未概念前不能引用。
with子句中不能嵌套概念<也确实是with子句中不能有with子句>，但子查询中显现的“子查询概念”语句能够引用已概念的子查询名称。
<能够引用前面已经概念的with子句> 。
复杂的查询会产生专门大的sql，withas语法显示一个个中间结果，显得有层次些，可读性与易保护性大为提高。
前面的中间结果能够被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时能够提升效率。
语法结构：
witht1as（...）,t2as（..）
二、字典预备
为了实现数据分层汇总交叉报表，需要成立行的层次结构表与列的交叉汇总对照表。
1、交叉汇总对照表：
一样分为，代码字段与代码汇总二个字段。
如：
SELECTfee_code,fee_stat_cateFROMFIN_COM_FEECODESTATWHEREREPORT_CODE='ZY11'
FEE_CIDE为数据表中的费用代码，FEE_STAT_CATE为费用汇总归类代码。
二、层次结构表：
一样分为，需要分层汇总的代码、汇总报表行题目、汇总报表排序代码、层次代码、分层汇总的父节点代码与报表显示标志。
如：
某报表按科室汇总产生分层报表的层次结构表如下：
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
肝胆研究院二
58
4
S015
1
37
S016
儿科
6
2
S001
1
38
0038
儿科
61
3
S016
1
39
0039
新生儿
62
3
S016
1
40
S008
妇产科
7
2
S001
1
41
0027
妇科
71
3
S008
1
42
0029
产科
72
3
S008
1
43
0024
耳鼻喉科
8
2
S017
1
44
0025
眼科
9
2
S017
1
45
0032
皮肤科
A
2
S001
1
46
0026
口腔科
A0
2
S017
1
SQL语句:
select*fromCOM_DEPT_FATHER
Dept_code科室代码dept_bane科室结构名称Sortid科室结构排序Rootlevel科室层次
Level_dept父节点代码SHOW_F报表显示列
三、实现方式
一、业务数据
如：
原始数据表：
FIN_IPB_FEEINFO
INHOS_DEPTCODE
FEE_CODE
TOT_COST
BALANCE_DATE
1
0013
055
2012-12-2908:
54:
11
2
0013
619
2012-12-2908:
54:
11
3
0013
181
2012-12-2908:
54:
11
4
0013
053
2012-12-2908:
54:
11
5
0013
181
2012-12-2908:
54:
11
6
0013
053
2012-12-2908:
54:
11
7
0013
141
2012-12-2908:
54:
11
8
0013
888
2012-12-2908:
54:
11
9
0013
001
2012-12-2908:
54:
11
10
0013
171
2012-12-2908:
54:
11
11
0013
171
2012-12-2908:
54:
11
12
0013
001
2012-12-2908:
54:
11
13
0013
620
2012-12-2908:
54:
11
14
0013
531
2012-12-2908:
54:
11
15
0013
055
2012-12-2908:
54:
11
16
0013
619
2012-12-2908:
54:
11
17
0013
171
2012-12-2908:
54:
11
18
0013
171
2012-12-2908:
54:
11
19
0013
221
2012-12-2908:
54:
11
20
0013
002
2012-12-2908:
54:
11
21
0013
001
2012-12-2908:
54:
11
22
0013
171
2012-12-2908:
54:
11
23
0001
531
2012-12-2908:
54:
11
24
0001
055
2012-12-2908:
54:
11
25
0001
620
2012-12-2908:
54:
11
26
0001
619
2012-12-2908:
54:
11
字段名称INHOS_DEPTCODE科室代码、FEE_CODE费用代码、TOT_COST金额、BALANCE_DATE统计日期
二、生成交叉表
利用交叉汇总对照表，生成各底层统计单位的交叉表。
SQL语句如下：
select,
count（distinctas人次,
sumas合计收入,
sum（decode,'01',,0））as药品收入,
sum（decode,'08',,0））as床位收入,
sum（decode,'02',,0））as检查收入,
sum（decode,'04',,0））as医治收入,
sum（decode,'05',,0））as手术收入,
sum（decode,'03',,0））as化验收入,
sum（decode,'06',,0））as卫生材料,
sum（decode,'09',,0））as诊察收入,
sum（decode,'10',,0））as护理收入,
sum（decode,'07',,0））as其他收入
fromFIN_IPB_FEEINFOa,
（SELECT*FROMFIN_COM_FEECODESTATWHEREREPORT_CODE='ZY11'）c
where='1'
AND=
and>=TO_DATE（'2021-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'）
AND<=TO_DATE（'2021-08-3123:
59:
59','yyyy-mm-ddhh24:
mi:
ss'）
groupby
结果如下：
3、层次记录汇总
依照层次结构表从底层别离向上汇总产生汇总记录。
例如如下：
3．1四层记录汇总
ROOTLEVEL=4的记录，依照LEVEL_DEPT生成对应的DEPT_CODE科室汇总记录。
如：
肝胆胰外科一病区、肝胆胰外科二病区……LEVEL_DEPT=S007汇总生成DEPT_CODE记录。
Level_4as（select,,,
t.人次,t.合计收入,t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERa,（
select,,
sum（a.人次）as人次,sum（a.合计收入）as合计收入,
sum（a.药品收入）as药品收入,sum（a.床位收入）as床位收入,
sum（a.检查收入）as检查收入,sum（a.医治收入）as医治收入,
sum（a.手术收入）as手术收入,sum（a.化验收入）as化验收入,
sum（a.卫生材料）as卫生材料,sum（a.诊察收入）as诊察收入,
sum（a.护理收入）as护理收入,sum（a.其他收入）as其他收入
fromdept_feea,COM_DEPT_FATHERt
where=and='4'groupbylevel_dept,rootlevel,level_dept
）twhere=,
3．2底层与四层记录归并
由于三层的汇总记录是由第四层汇总记录与第三层底层记录汇总取得，因此需要将底层与四层记录归并后再进行汇总。
Level_34as（select,,,
t.人次,t.合计收入,
t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERainnerjoindept_feeton=
union
select*fromLevel_4orderbysortid）
此语句是将底层记录整理后行第四层记录归并。
3．3三层记录汇总
ROOTLEVEL=3的记录，依照LEVEL_DEPT生成对应的DEPT_CODE科室汇总记录。
如：
LEVEL_DEPT=‘S005’消化内科、血液内科…..LEVEL_DEPT=S005生成内科汇总记录。
sum_34as（select,,
sum（a.人次）as人次,sum（a.合计收入）as合计收入,
sum（a.药品收入）as药品收入,sum（a.床位收入）as床位收入,
sum（a.检查收入）as检查收入,sum（a.医治收入）as医治收入,
sum（a.手术收入）as手术收入,sum（a.化验收入）as化验收入,
sum（a.卫生材料）as卫生材料,sum（a.诊察收入）as诊察收入,
sum（a.护理收入）as护理收入,sum（a.其他收入）as其他收入
fromLevel_34awhererootlevel='3'groupbylevel_dept,rootlevel,level_dept）
底层与三四层记录归并
将底层Level_4、三四层记录sum_34表整理归并。
Level_234as（select*from（select,,,
t.人次,t.合计收入,
t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERainnerjoindept_feeton=
union
select*fromLevel_4orderbysortid）
union
select,,,
t.人次,t.合计收入,
t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERa,sum_34t
where=
4、层次记录汇总例如
SQL查询语句
withdept_feeas（select,
count（distinctas人次,
sumas合计收入,
sum（decode,'01',,0））as药品收入,
sum（decode,'08',,0））as床位收入,
sum（decode,'02',,0））as检查收入,
sum（decode,'04',,0））as医治收入,
sum（decode,'05',,0））as手术收入,
sum（decode,'03',,0））as化验收入,
sum（decode,'06',,0））as卫生材料,
sum（decode,'09',,0））as诊察收入,
sum（decode,'10',,0））as护理收入,
sum（decode,'07',,0））as其他收入
fromFIN_IPB_FEEINFOa,
（SELECT*FROMFIN_COM_FEECODESTATWHEREREPORT_CODE='ZY11'）c
where='1'
AND=
and>=TO_DATE（'2021-07-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'）
AND<=TO_DATE（'2021-07-3123:
59:
59','yyyy-mm-ddhh24:
mi:
ss'）
groupby
）,
Level_4as（select,,,
t.人次,t.合计收入,t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERa,（
select,,
sum（a.人次）as人次,sum（a.合计收入）as合计收入,
sum（a.药品收入）as药品收入,sum（a.床位收入）as床位收入,
sum（a.检查收入）as检查收入,sum（a.医治收入）as医治收入,
sum（a.手术收入）as手术收入,sum（a.化验收入）as化验收入,
sum（a.卫生材料）as卫生材料,sum（a.诊察收入）as诊察收入,
sum（a.护理收入）as护理收入,sum（a.其他收入）as其他收入
fromdept_feea,COM_DEPT_FATHERt
where=and='4'groupbylevel_dept,rootlevel,level_dept
）twhere=,
Level_34as（select,,,
t.人次,t.合计收入,
t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERainnerjoindept_feeton=
union
select*fromLevel_4orderbysortid）,
sum_34as（select,,
sum（a.人次）as人次,sum（a.合计收入）as合计收入,
sum（a.药品收入）as药品收入,sum（a.床位收入）as床位收入,
sum（a.检查收入）as检查收入,sum（a.医治收入）as医治收入,
sum（a.手术收入）as手术收入,sum（a.化验收入）as化验收入,
sum（a.卫生材料）as卫生材料,sum（a.诊察收入）as诊察收入,
sum（a.护理收入）as护理收入,sum（a.其他收入）as其他收入
fromLevel_34awhererootlevel='3'groupbylevel_dept,rootlevel,level_dept）,
Level_234as（select*from（select,,,
t.人次,t.合计收入,
t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERainnerjoindept_feeton=
union
select*fromLevel_4orderbysortid）
union
select,,,
t.人次,t.合计收入,
t.药品收入,t.床位收入,t.检查收入,t.医治收入,
t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入,
fromCOM_DEPT_FATHERa,sum_34t
where=
select,,
人次,合计收入,
药品收入,床位收入,检查收入,医治收入,
手术收入,化验收入,卫生材料,诊察收入,护理收入,其他收入
fromLevel_234t,COM_DEPT_FATHERawhere=and='1'
union
--本月合计
select,,
a.人次,a.合计收入,a.药品收入,a.床位收入,a.检查收入,
a.医治收入,a.手术收入,a.化验收入,a.卫生材料,a.诊察收入,
a.护理收入,a.其他收入
fromCOM_DEPT_FATHERt,
（selectsum（人次）as人次,sum（合计收入）as合计收入,sum（药品收入）as药品收入,
sum（床位收入）as床位收入,sum（检查收入）as检查收入,
sum（医治收入）as医治收入,sum（手术收入）as手术收入,
sum（化验收入）as化验收入,sum（卫生材料）as卫生材料,
sum（诊察收入）as诊察收入,sum（护理收入）as护理收入,
sum（其他收入）as其他收入fromdept_fee）a
where='S001'
由于用户有时不需要在报表中显示某些底层记录，能够在层次结构设置show_f字段过滤不显示的底层记录。
四、另类实现方式
一、报表样式
序号
科室名称
总计
小计
普诊
专家
急诊
0000
本年到本月累计
448373
420765
311082
109683
27608
0001
本月合计
105467
99416
73534
25882
6051
001
内科
21901
19583
13456
6127
2318
002
其中：
心血管
3067
3064
2074
990
3
003
消化
3578
3577
1649
1928
1
004
呼吸
2681
2681
1602
1079
0
005
内分泌
3347
3346
2057
1289
1
006
血液
894
894
622
272
0
007
肾病
1340
1340
819
521
0
008
外科
16186
14197
10431
3766
1989
009
其中：
普外
2833
2833
1787
1046
0
010
骨外
3587
3585
2351
1234
2
011
泌尿男科
2196
2194
1090
1104
2
012
脑外
230
229
150
79
1
013
胸外
254
254
159
95
0
014
疼痛
310
310
309
1
0
015
肝胆外科
359
359
174
185
0
016
妇产科
9756
9642
6288
3354
114
017
其中：
生殖医学中心
1732
1732
升级会员 