oracle sql

用 oracle 的 sql 实现 的报表格式

 

以前常遇到一些这样的问题，需要在jsp页面或者报表上面显示某一年的数据统计。表里面是数据流水，我们需要的结果是：

 

 

一月流水，

一月流水，

一月流水，

一月小计，

二月流水，

二月流水，

二月流水，

二月小计，

.

.

.

合计。

 

 

这种显示方式可以有多种实现方式，java，jsp，报表内部的库等，下面看看仅用sql是如何实现的。代码是以前工作中的代码，先贴出来，有空再简化为更直观的。

 

with
 condition as (select ? begin_date,? end_date from dual),
 t      as (
 SELECT -1 flag, f.bps_flow_no bps_flow_no
   FROM POS_CONSUME_FLOW f
  where f.STATE = 'D'  AND f.TRADE_NO = 'T00004'

 union all

 SELECT 1 flag, f.bps_flow_no bps_flow_no
   FROM POS_CONSUME_FLOW f
   where f.STATE='A'
    or (f.state='D' and f.trade_no='T00002')
)
select rownum rn,
       jur_bank,
       mer_no,
       pos_no,
       card_no,
       cut_scale,
       local_register_date,
       c_amount,
       c_accounte,
       shouxu,
       merfendan,
       mer_scale,
       huazhang
from(
(
select
       a.jur_bank||a.mer_no||b.pos_no||'1'||to_char(b.local_register_date,'yyyymmdd') myorder,
       a.jur_bank,
       a.mer_no,
       b.pos_no pos_no,
       b.card_no,
       a.cut_scale,
       b.local_register_date,
       nvl(t.flag,1)*nvl(b.c_amount,0) c_amount,
       nvl(t.flag,1)*nvl(b.c_accounte,0) c_accounte,
       nvl(t.flag,1)*nvl(b.c_amount,0)*(1-nvl(a.mer_scale,0))*a.cut_scale shouxu,
       nvl(t.flag,1)*nvl(b.c_amount,0)*a.mer_scale    merfendan,
       a.mer_scale     mer_scale,
       nvl(t.flag,1)*( nvl(b.c_amount,0) - nvl(b.c_amount,0)*a.mer_scale - nvl(b.c_amount,0)*(1-nvl(a.mer_scale,0))*a.cut_scale ) huazhang
  from t_merchant a, pos_consume_flow b,t t,condition c
 where a.mer_no = b.merchant_no
 and b.bps_flow_no=t.bps_flow_no
 and b.c_amount!=0
 and b.local_register_date >=to_date(c.begin_date,'yyyy-mm-dd')
 and b.local_register_date<=to_date(c.end_date,'yyyy-mm-dd')
 and b.STATE in ('A','D')
union all

  select
       a.jur_bank||a.mer_no||b.pos_no||'2' myorder,
       a.jur_bank,
       a.mer_no mer_no,
       '小计：' pos_no,
       null,
       max(a.cut_scale),
       null,
       sum(nvl(t.flag,1)*nvl(b.c_amount,0)) c_amount,
       sum(nvl(t.flag,1)*nvl(b.c_accounte,0)) c_accounte,
       sum(nvl(t.flag,1)*nvl(b.c_amount,0)*(1-nvl(a.mer_scale,0))*a.cut_scale)    shouxu,
       sum(nvl(t.flag,1)*nvl(b.c_amount,0)*a.mer_scale)    merfendan,
       max(a.mer_scale)     mer_scale,
       sum(nvl(t.flag,1)*( nvl(b.c_amount,0) - nvl(b.c_amount,0)*a.mer_scale - nvl(b.c_amount,0)*(1-nvl(a.mer_scale,0))*a.cut_scale )) huazhang
from t_merchant a, pos_consume_flow b,t t,condition c
 where a.mer_no = b.merchant_no
  and b.bps_flow_no=t.bps_flow_no
  and b.c_amount!=0
 and b.local_register_date >=to_date(c.begin_date,'yyyy-mm-dd')
 and b.local_register_date<=to_date(c.end_date,'yyyy-mm-dd')
 and b.STATE in ('A','D')
  group by a.jur_bank,a.mer_no,b.pos_no

union all

select
       a.jur_bank||a.mer_no||'3' myorder,
       a.jur_bank,
       a.mer_no mer_no,
       '合计 ： pos_no,
       null,
       max(a.cut_scale),
       null,
       sum(nvl(t.flag,1)*nvl(b.c_amount,0)) c_amount,
       sum(nvl(t.flag,1)*nvl(b.c_accounte,0)) c_accounte,
       sum(nvl(t.flag,1)*nvl(b.c_amount,0)*(1-nvl(a.mer_scale,0))*a.cut_scale)    shouxu,
       sum(nvl(t.flag,1)*nvl(b.c_amount,0)*a.mer_scale)    merfendan,
       max(a.mer_scale)     mer_scale,
       sum(nvl(t.flag,1)*( nvl(b.c_amount,0) - nvl(b.c_amount,0)*a.mer_scale - nvl(b.c_amount,0)*(1-nvl(a.mer_scale,0))*a.cut_scale )) huazhang
from t_merchant a, pos_consume_flow b,t t,condition c
 where a.mer_no = b.merchant_no
  and b.bps_flow_no=t.bps_flow_no
  and b.c_amount!=0
 and b.local_register_date >=to_date(c.begin_date,'yyyy-mm-dd')
 and b.local_register_date<=to_date(c.end_date,'yyyy-mm-dd')
 and b.STATE in ('A','D')
  group by a.jur_bank,a.mer_no

order by myorder

)
)

 

实际上就是用union all 和自定义派序列 myorder 来实现的，myorder利用了oracle对varchar2的排序是先比较第一个字符，然后比较第二个，以此类推。比如：  'a19999999'  比 'a2' 小。