给你一个例子你参考一下先。在ORACLE中利用视图组织矩阵报表 笔者曾参加过日照港物资MIS和生产MIS的开发工作。在实际开发过程中,利用Oracle前端开发工具之一Oracle*Reports做过许多矩阵报表。所谓矩阵报表是由一个列记录值、一个行记录值和一个cell组,这组内的每一个格包含了行与列相关的数据。笔者认为矩阵报表实现的关键在于组织数据。一、利用视图组织矩阵报表:
1、本例假设有一物资资金统计表,其格式如下:项目 类别 期初 库存 本期 增加 本期减少 一公司 二公司 三公司 ... 期末库存
黑色金属
有色金属
五金制品
非金属建材
橡塑
工具
劳保
杂品
机电设备 该表横向显示“期初库存”、“本期增加”、“本期减少及消耗去向”、“期末库存”;纵向显示物资类别,是一矩阵报表。与该报表有关的数据基表有以下几个:.RQ:存放统计日期,其数据结构为:
字段名 含义 长度 类型 备注
YF 统计月份 6 CHAR 格式:YYYYMM.LBXX:存放物资类别信息,其数据结构为:
字段名 含义 长度 类型 备注
LBDM 类别代码 2 CHAR 取值范围01~09
LBMC 类别名称 20 CHAR .DWDM:存放领料单位代码,其数据结构为:
字段名 含义 长度 类型 备注
DWDM 单位代码 4 CHAR 取值范围0101~0199
DWMC 单位名称 20 CHAR .YDKCXX:存放库存月度备份信息,其数据结构如下:
字段名 含义 长度 类型
YDH 月份 6 CHAR
LBDM 类别代码 2 CHAR
KCJE 月底库存 12,2 NUMBER
YRJE 月入金额 12,2 NUMBER
YCJE 月出金额 12,2 NUMBER.XHQX:存放资金消耗去向信息,其数据结构如下:
字段名 含义 长度 类型 备注
RQ 领料日期 10 DATE
WZDM 物资代码 9 CHAR 前两位代表物资 所属大类
DWDM 单位代码 4 CHAR
XHJE 消耗金额 12,2 NUMBER 2、利用视图组织矩阵报表的过程:CREATE VIEW V_ZHBB1
AS SELECT LBDM,(KCJE-YRJE+YCJE)"QCJE" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB2
AS SELECT LBDM,YRJE"BQZJ" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB3
AS SELECT LBDM,YCJE"BQJS" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB4
AS SELECT LBDM,KCJE"QMJE" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB5
AS SELECT DWDM,SUBSTR(WZDM,1,2)"LBDM",SUM(XHJE)"LLJE"
FROM XHQX,RQ
WHERE TO_CHAR(RQ,’YYYYMM’)=YF
GROUP BY DWDM,SUBSTR(WZDM,1,2);CREATE VIEW V_ZHBB AS
SELECT 0.1"DWDM",'期初金额'"DWMC",V_ZHBB1.LBDM,LBMC,SUM(QCJE)"JE"
FROM V_ZHBB1,LBXX WHERE V_ZHBB1.LBDM=LBXX.LBDM
GROUP BY V_ZHBB1.LBDM,LBMC
UNION
SELECT 0.2"DWDM",'本期增加'"DWMC",V_ZHBB2.LBDM,LBMC,SUM(BQZJ)"JE"
FROM V_ZHBB2,LBXX WHERE V_ZHBB2.LBDM=LBXX.LBDM
GROUP BY V_ZHBB2.LBDM,LBMC
UNION
SELECT 0.3"DWDM",'本期减少'"DWMC",V_ZHBB3.LBDM,LBMC,SUM(BQJS)"JE"
FROM V_ZHBB3,LBXX WHERE V_ZHBB3.LBDM=LBXX.LBDM
GROUP BY V_ZHBB3.LBDM,LBMC
UNION
SELECT TO_NUMBER(V_ZHBB5.DWDM)"DWDM",DWMC,V_ZHBB5.LBDM,LBMC,LLJE"JE"
FROM V_ZHBB5,DWDM,LBXX
WHERE V_ZHBB5.DWDM=DWDM.DWDM AND V_ZHBB5.LBDM=LBXX.LBDM
UNION
SELECT 200"DWDM",'期末金额'"DWMC",V_ZHBB4.LBDM,LBMC,SUM(QMJE)"JE"
FROM V_ZHBB4,LBXX WHERE V_ZHBB4.LBDM=LBXX.LBDM
GROUP BY V_ZHBB4.LBDM,LBMC;
至此该矩阵报表的数据组织完毕,建立矩阵报表查询时直接引用视图V_ZHBB即可。3、需要注意的问题:(1) 由于UNION操作当有两行完全相同的记录时,只取其中的一行,这样就造成汇总数据不准确,所以设计视图时必须避免这种情况的发生。其方法是:在同一查询中,可加入主键字段加以区分;在不同查询中,可加入一个不同值的常量来区分。
(2) 当所建视图的级数或UNION次数太多时,系统会出现“打开游标数太多”的错误。这时就需要用一个中间表将某一级视图数据转储一下,接下来所建视图再引用中间表的数据。
(3) 在每次生成报表之前,必须先删除表RQ中的原有日期,将新的统计日期追加进去并提交,再删除中间表的内容,然后将需转储的视图数据追加进去并提交。
2、 结论:
利用视图组织矩阵报表,其思路是将报表中的列利用视图旋转成行,然后再用并操作(UNION)将这些行连接起来。对于列值来自两个以上的数据表或列数不确定(如上例中每月资金消耗去向)的报表,这种方法非常有效,并且在维护报表时不需要修改程序。
1、本例假设有一物资资金统计表,其格式如下:项目 类别 期初 库存 本期 增加 本期减少 一公司 二公司 三公司 ... 期末库存
黑色金属
有色金属
五金制品
非金属建材
橡塑
工具
劳保
杂品
机电设备 该表横向显示“期初库存”、“本期增加”、“本期减少及消耗去向”、“期末库存”;纵向显示物资类别,是一矩阵报表。与该报表有关的数据基表有以下几个:.RQ:存放统计日期,其数据结构为:
字段名 含义 长度 类型 备注
YF 统计月份 6 CHAR 格式:YYYYMM.LBXX:存放物资类别信息,其数据结构为:
字段名 含义 长度 类型 备注
LBDM 类别代码 2 CHAR 取值范围01~09
LBMC 类别名称 20 CHAR .DWDM:存放领料单位代码,其数据结构为:
字段名 含义 长度 类型 备注
DWDM 单位代码 4 CHAR 取值范围0101~0199
DWMC 单位名称 20 CHAR .YDKCXX:存放库存月度备份信息,其数据结构如下:
字段名 含义 长度 类型
YDH 月份 6 CHAR
LBDM 类别代码 2 CHAR
KCJE 月底库存 12,2 NUMBER
YRJE 月入金额 12,2 NUMBER
YCJE 月出金额 12,2 NUMBER.XHQX:存放资金消耗去向信息,其数据结构如下:
字段名 含义 长度 类型 备注
RQ 领料日期 10 DATE
WZDM 物资代码 9 CHAR 前两位代表物资 所属大类
DWDM 单位代码 4 CHAR
XHJE 消耗金额 12,2 NUMBER 2、利用视图组织矩阵报表的过程:CREATE VIEW V_ZHBB1
AS SELECT LBDM,(KCJE-YRJE+YCJE)"QCJE" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB2
AS SELECT LBDM,YRJE"BQZJ" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB3
AS SELECT LBDM,YCJE"BQJS" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB4
AS SELECT LBDM,KCJE"QMJE" FROM YDKCXX,RQ WHERE YDH=YF;CREATE VIEW V_ZHBB5
AS SELECT DWDM,SUBSTR(WZDM,1,2)"LBDM",SUM(XHJE)"LLJE"
FROM XHQX,RQ
WHERE TO_CHAR(RQ,’YYYYMM’)=YF
GROUP BY DWDM,SUBSTR(WZDM,1,2);CREATE VIEW V_ZHBB AS
SELECT 0.1"DWDM",'期初金额'"DWMC",V_ZHBB1.LBDM,LBMC,SUM(QCJE)"JE"
FROM V_ZHBB1,LBXX WHERE V_ZHBB1.LBDM=LBXX.LBDM
GROUP BY V_ZHBB1.LBDM,LBMC
UNION
SELECT 0.2"DWDM",'本期增加'"DWMC",V_ZHBB2.LBDM,LBMC,SUM(BQZJ)"JE"
FROM V_ZHBB2,LBXX WHERE V_ZHBB2.LBDM=LBXX.LBDM
GROUP BY V_ZHBB2.LBDM,LBMC
UNION
SELECT 0.3"DWDM",'本期减少'"DWMC",V_ZHBB3.LBDM,LBMC,SUM(BQJS)"JE"
FROM V_ZHBB3,LBXX WHERE V_ZHBB3.LBDM=LBXX.LBDM
GROUP BY V_ZHBB3.LBDM,LBMC
UNION
SELECT TO_NUMBER(V_ZHBB5.DWDM)"DWDM",DWMC,V_ZHBB5.LBDM,LBMC,LLJE"JE"
FROM V_ZHBB5,DWDM,LBXX
WHERE V_ZHBB5.DWDM=DWDM.DWDM AND V_ZHBB5.LBDM=LBXX.LBDM
UNION
SELECT 200"DWDM",'期末金额'"DWMC",V_ZHBB4.LBDM,LBMC,SUM(QMJE)"JE"
FROM V_ZHBB4,LBXX WHERE V_ZHBB4.LBDM=LBXX.LBDM
GROUP BY V_ZHBB4.LBDM,LBMC;
至此该矩阵报表的数据组织完毕,建立矩阵报表查询时直接引用视图V_ZHBB即可。3、需要注意的问题:(1) 由于UNION操作当有两行完全相同的记录时,只取其中的一行,这样就造成汇总数据不准确,所以设计视图时必须避免这种情况的发生。其方法是:在同一查询中,可加入主键字段加以区分;在不同查询中,可加入一个不同值的常量来区分。
(2) 当所建视图的级数或UNION次数太多时,系统会出现“打开游标数太多”的错误。这时就需要用一个中间表将某一级视图数据转储一下,接下来所建视图再引用中间表的数据。
(3) 在每次生成报表之前,必须先删除表RQ中的原有日期,将新的统计日期追加进去并提交,再删除中间表的内容,然后将需转储的视图数据追加进去并提交。
2、 结论:
利用视图组织矩阵报表,其思路是将报表中的列利用视图旋转成行,然后再用并操作(UNION)将这些行连接起来。对于列值来自两个以上的数据表或列数不确定(如上例中每月资金消耗去向)的报表,这种方法非常有效,并且在维护报表时不需要修改程序。
解决方案 »
- 窗口函数在update里不起作用
- 用linux c++如何保证commit成功提交事务??在线等高手解决!
- 请教expdp导出exclude参数的问题,怎么前后日志和dump文件大小一样?
- 谁能给我一个oracle论坛的邀请码呀
- 如何利用Object Browser图形化工具提高Oracle开发工作效率(三)数据库管理篇
- Linux下如何重启Oracle数据库服务和监听器服务?详见内容,求求大家了,帮帮我好吗!!
- 表中纪录中最大日期的sql怎么写?
- 请说出数据连接池的工作机制是什么?
- 关于oracle数组处理--热盼中
- 100分求删除sql,分不够再开贴给分
- 8.17i的版本怎么样才能安装到win2 server上去啊
- ou的tns坏了,能否之重新按装一下?在线等待
select ... ,sum(decode(.....)),... from a
group by ....