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

1、数据分层汇总交叉报表SQL语句实现方式数据分层汇总交叉报表SQL语句实现方式在治理系统中，治理人员往往需要对业务数据进行不同需求的分层汇总，并产生各类形式交叉报表。为了实现此类报表，程序员需要构造层次结构超级复杂的SQL语句，乃至利用前台编程工具或其它报表工具来完成。以下通过二个实例，介绍此类报表的实现方式。一、WITH as 语句利用WITHAS语句能够为一个子查询语句块概念一个名称，利用那个子查询名称能够在查询语句的很多地址引用那个子查询。Oracle数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到必然的优化作用。with子句是9i新增语法。你能够在任何一个顶层的SELEC

2、T语句和几乎所有类型的子查询语句前，利用子查询概念子句。被概念的子查询名称能够在主查询语句和所有的子查询语句中引用，但未概念前不能引用。with子句中不能嵌套概念，但子查询中显现的“子查询概念”语句能够引用已概念的子查询名称。复杂的查询会产生专门大的sql，with as语法显示一个个中间结果，显得有层次些，可读性与易保护性大为提高。前面的中间结果能够被语句中的select或后面的中间结果表引用，类似于一个范围仅限于本语句的临时表，在需要多次查询某中间结果时能够提升效率。语法结构：with t1 as (.),t2 as (.) 二、字典预备 为了实现数据分层汇总交叉报表，需要成立行的层次结构

3、表与列的交叉汇总对照表。1、交叉汇总对照表：一样分为，代码字段与代码汇总二个字段。如：SELECT fee_code,fee_stat_cate FROM FIN_COM_FEECODESTAT WHERE REPORT_CODE = ZY11FEE_CIDE为数据表中的费用代码，FEE_STAT_CATE为费用汇总归类代码。二、层次结构表：一样分为，需要分层汇总的代码、汇总报表行题目、汇总报表排序代码、层次代码、分层汇总的父节点代码与报表显示标志。如：某报表按科室汇总产生分层报表的层次结构表如下： DEPT_CODEDEPT_NAMESORTIDROOTLEVELLEVEL_DEPTSHOW

4、_F1S001本月合计012S10012S005内科12S001130014 消化内科113S005140015 血液内科123S005150011 内科心血管133S005160013 内分泌科143S005170016 呼吸内科153S005180017 肾病内科163S00519S006综合科22S0011100033 综合一213S0061110034 综合二223S0061120035 综合三233S0061130036 综合四243S0061140048 综合五253S0061150037 综合六263S0061160019感染科32S0011170018神经内科42S005118

5、S002外科52S001119S007 普外科513S0021200042 肝胆胰外科一病区51014S0071210002 肝胆胰外科二病区51024S0071220001 甲乳疝血管外科病区51034S0071230003 结直肠肛门外科病区51044S0071240004 胸外科523S0021250006 泌尿外科52023S0021260005 脑外科52033S002127S003 骨科533S0021280009 创伤修复病区53014S0031290008 显微、手外科病区53024S0031300040 脊柱外科一病区53034S0031310044 骨肿瘤、脊柱外科二病区5

6、3044S0031320045 关节外科病区53054S0031330046 创伤骨科病区53064S003134S015 肝胆研究院543S0021350054 肝胆研究院一574S0151360079 肝胆研究院二584S015137S016儿科62S0011380038 儿科613S0161390039 新生儿623S016140S008妇产科72S0011410027 妇科713S0081420029 产科723S0081430024耳鼻喉科82S0171440025眼科92S0171450032皮肤科A2S0011460026口腔科A02S0171SQL语句: select * fr

7、om COM_DEPT_FATHERDept_code 科室代码 dept_bane 科室结构名称 Sortid科室结构排序 Rootlevel 科室层次Level_dept 父节点代码 SHOW_F报表显示列三、实现方式一、业务数据如：原始数据表：FIN_IPB_FEEINFO INHOS_DEPTCODEFEE_CODETOT_COSTBALANCE_DATE100130552012-12-29 08:54:11200136192012-12-29 08:54:11300131812012-12-29 08:54:11400130532012-12-29 08:54:11500131812

8、012-12-29 08:54:11600130532012-12-29 08:54:11700131412012-12-29 08:54:11800138882012-12-29 08:54:11900130012012-12-29 08:54:111000131712012-12-29 08:54:111100131712012-12-29 08:54:111200130012012-12-29 08:54:111300136202012-12-29 08:54:111400135312012-12-29 08:54:111500130552012-12-29 08:54:11160013

9、6192012-12-29 08:54:111700131712012-12-29 08:54:111800131712012-12-29 08:54:111900132212012-12-29 08:54:112000130022012-12-29 08:54:112100130012012-12-29 08:54:112200131712012-12-29 08:54:112300015312012-12-29 08:54:112400010552012-12-29 08:54:112500016202012-12-29 08:54:112600016192012-12-29 08:54:

10、11字段名称INHOS_DEPTCODE 科室代码、FEE_CODE费用代码、TOT_COST金额、BALANCE_DATE统计日期二、生成交叉表 利用交叉汇总对照表，生成各底层统计单位的交叉表。SQL语句如下：select , count(distinct as 人次, sum as 合计收入, 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 手术收入,

11、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 其他收入 from FIN_IPB_FEEINFO a, (SELECT * FROM FIN_COM_FEECODESTAT WHERE REPORT_CODE = ZY11) c where = 1 AND = and = TO_DATE(2021-08-01 00:00:00,yyyy-mm-dd hh2

12、4:mi:ss) AND = TO_DATE(2021-07-01 00:00:00,yyyy-mm-dd hh24:mi:ss) AND = TO_DATE(2021-07-31 23:59:59,yyyy-mm-dd hh24:mi:ss) group by ),Level_4 as (select , t.人次,t.合计收入,t.药品收入,t.床位收入,t.检查收入,t.医治收入, t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入, , from COM_DEPT_FATHER a,( select , sum(a.人次) as 人次,sum(a.合计收

13、入) 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 其他收入 from dept_fee a,COM_DEPT_FATHER t where = and =4 group by level_dept,rootlevel,level

14、_dept) t where =,Level_34 as(select , t.人次,t.合计收入, t.药品收入,t.床位收入,t.检查收入,t.医治收入, t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入, , from COM_DEPT_FATHER a inner join dept_fee t on =unionselect * from Level_4 order by sortid),sum_34 as (select , sum(a.人次) as 人次,sum(a.合计收入) as 合计收入, sum(a.药品收入) as 药品收入,sum(a

15、.床位收入) 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 其他收入 from Level_34 a where rootlevel=3 group by level_dept,rootlevel,level_dept),Level_234 as(select * from (select , t.人次,t.合

16、计收入, t.药品收入,t.床位收入,t.检查收入,t.医治收入, t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入, , from COM_DEPT_FATHER a inner join dept_fee t on =unionselect * from Level_4 order by sortid)unionselect , t.人次,t.合计收入, t.药品收入,t.床位收入,t.检查收入,t.医治收入, t.手术收入,t.化验收入,t.卫生材料,t.诊察收入,t.护理收入,t.其他收入, , from COM_DEPT_FATHER a, sum_

17、34 twhere =select , 人次,合计收入, 药品收入,床位收入,检查收入,医治收入, 手术收入,化验收入,卫生材料,诊察收入,护理收入,其他收入 from Level_234 t,COM_DEPT_FATHER a where = and =1union-本月合计select ,a.人次,a.合计收入,a.药品收入,a.床位收入,a.检查收入,a.医治收入,a.手术收入,a.化验收入,a.卫生材料,a.诊察收入,a.护理收入,a.其他收入 from COM_DEPT_FATHER t, (select sum(人次) as 人次,sum(合计收入) as 合计收入,sum(药品收

18、入) as 药品收入, sum(床位收入) as 床位收入,sum(检查收入) as 检查收入, sum(医治收入) as 医治收入,sum(手术收入) as 手术收入, sum(化验收入) as 化验收入,sum(卫生材料) as 卫生材料, sum(诊察收入) as 诊察收入,sum(护理收入) as 护理收入, sum(其他收入) as 其他收入 from dept_fee) awhere =S001由于用户有时不需要在报表中显示某些底层记录，能够在层次结构设置show_f字段过滤不显示的底层记录。四、另类实现方式一、报表样式序号科室名称总计小计普诊专家急诊0000本年到本月累计4483

19、73420765311082109683276080001本月合计1054679941673534258826051001内科21901195831345661272318002 其中：心血管3067306420749903003 消化35783577164919281004 呼吸26812681160210790005 内分泌33473346205712891006 血液8948946222720007 肾病134013408195210008外科16186141971043137661989009 其中：普外28332833178710460010 骨外35873585235112342011 泌尿男科21962194109011042012 脑外230229150791013 胸外254254159950014 疼痛31031030910015 肝胆外科3593591741850016妇产科9756964262883354114017 其中：生殖医学中心17321732