前几天客户要下一个报表.数据大概是2000条,报表结果是要进行多次统计的.如把统计逻辑放到程序中是不可选的.我试了下.下个报表要2分钟.我试了下视图只用了1秒不到.(视图当然会比应用程序快这是一定的.关键是怎样做到统计逻辑在视图中实现)下面介绍下:
逻辑是这样的:
A表是用户信息表.B表是用户积分情况表,C表是用户积分使用情况表,B表有两种状态.1-用户可使用的积分,2-用户还不能使用的积分,C表有2种状态.1-已使用了的积分数据,2-已冻结的积分数据,现在要求用户的总积分数,已使用积分数,冻结的积分数,还可使用积分数.
我是这样做的:
sql 代码
- create or replace view integral_sum_view as
- select "YGJL_PERSONNEL_ID","P_MOBILE","SUMNUMBER","SUMNUMBERAFFIRM","SUMNUMBERPUT","INTEGRAL" from (
- select a.YGJL_PERSONNEL_ID as YGJL_PERSONNEL_ID
- decode(b.sumNumber,null,0,b.sumNumber) as sumNumber,--积分总数
- decode(c.sumNumberAffirm,null,0,c.sumNumberAffirm) as sumNumberAffirm,--已销减积分
- decode(d.sumNumberPut,null,0,d.sumNumberPut) as sumNumberPut,--当前冻结积分
- ( (to_number(decode(b.sumNumber,null,0,b.sumNumber))-
- to_number(decode(c.sumNumberAffirm,null,0,c.sumNumberAffirm)))-
- to_number(decode(d.sumNumberPut,null,0,d.sumNumberPut))) as INTEGRAL--可用积分
- from A a left join(
- --统计积分总数
- select this_.YGJL_PERSONNEL_ID, sum( decode(this_.ITEM_INTEGRAL,null,0,this_.item_integral) ) as sumNumber from
- B this_ where this_.STATE='3' group by (this_.YGJL_PERSONNEL_ID)
- ) b on a.ygjl_personnel_id = b.ygjl_personnel_id left join(
- --统计已销减积分
- select this_.YGJL_PERSONNEL_ID, sum( decode(this_.TRADE_INTEGRAL,null,0,this_.TRADE_INTEGRAL) ) as sumNumberAffirm from
- C this_, tygjldept5_ where this_.STATE='1'
- group by (this_.YGJL_PERSONNEL_ID)) c on a.ygjl_personnel_id = c.ygjl_personnel_id left join(
- --统计当前审请积分
- select this_.YGJL_PERSONNEL_ID, sum( decode(this_.TRADE_INTEGRAL,null,0,this_.TRADE_INTEGRAL) ) as sumNumberPut from
- T_INTEGRAL_TRADE this_,
- and not this_.STATE='2' group by (this_.YGJL_PERSONNEL_ID)) d on a.ygjl_personnel_id = d.ygjl_personnel_id
- )