oracle的报表的典型sql语句

xlyslgoabw

实现SQL语句

Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='04')-1,'')
  over(Order By m.app_no) appno,
  m.feature_seq||'' feature_seq,m.month||'' month,m.pointfee,m.discount
  From app_mtfeature m Where m.app_no='04'
Union All
  Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='04'
Union All
  Select LEAD(m.app_no,(Select Count(1) From app_mtfeature Where app_no='06')-1,'')
  over(Order By m.app_no) appno,
   m.feature_seq||'',m.month||'',m.pointfee,m.discount
   From app_mtfeature m Where m.app_no='06'
Union All
  Select '','合计','',Sum(pointfee),Sum(discount) From app_mtfeature Where app_no='06'
zxbxiaobo@sina.com pwd:xiaobo
看看首项，第二项效果怎么样!

创建包,包体,使用存储过程 返回游标集合

CREATE OR REPLACE PACKAGE Acc_Card1  ---创建一包
  As
    Type Acc_Card_cursor Is Ref Cursor;
    Procedure transact_data_query1(p_cursor Out Acc_Card_cursor,v_merchant_no varchar2,v_point_no varchar2,v_terminal_no varchar2,v_card_type varchar2,v_account_date1 date,v_account_date2 date,v_transact_time1 date,v_transact_time2 date,v_card_logical_number varchar2,v_card_face_no varchar2);
END Acc_Card1;

CREATE OR REPLACE Package Body Acc_Card1 As  --创建一包体
Procedure transact_data_query1(p_cursor Out Acc_Card_cursor,v_merchant_no varchar2,v_point_no varchar2,v_terminal_no varchar2,v_card_type varchar2,v_account_date1 date,v_account_date2 date,v_transact_time1 date,v_transact_time2 date,v_card_logical_number varchar2,v_card_face_no varchar2) Is
Begin
Open p_Cursor For
Select
to_char(v1.TRANSACT_TIME-4/24,'yyyy-mm-dd') as transact_date
,v1.merchant_no as merchant_no
,substr(v1.POINT_NO,0,2) point_no
,substr(v1.POINT_NO,3,2)  terminal_no
,v1.psam_no,v1.card_type,v1.card_type_name,v1.card_face_no,v1.card_logical_number,v1.tran_merchant_name,v1.tran_merchant_no,
v1.app_no,v1.transact_name,v1.transact_type,v1.mid_transact_value,v1.mid_voucher_value,v1.transact_time,v1.account_date,v1.customer_id
From v_card_tran_query v1
where
('-1'=v_card_face_no or card_face_no=v_card_face_no)
and  ('-1'=v_card_logical_number or card_logical_number=v_card_logical_number)      
and  ('-1'=v_merchant_no or merchant_no=v_merchant_no)
and  ('-1'=v_point_no or substr(v1.POINT_NO,0,2)=v_point_no)
and  ('-1'=v_terminal_no or substr(v1.POINT_NO,3,2)=v_terminal_no)
and  ('-1'=v_card_type or card_type=v_card_type)
and v1.account_date between v_account_date1 and v_account_date2
and v1.transact_time between v_transact_time1 and v_transact_time2
and
(  v1.partition_field='00'    or
exists
   (
      select * from sys_partition a where
      a.start_date between  v_account_date1 and v_account_date2
      or a.end_date between  v_account_date1 and v_account_date2
      or v_account_date1 between a.start_date and a.end_date
      or v_account_date2 between a.start_date and a.end_date
   )
)
  union all
  select  
  decode(grouping(transact_date),1,' 合計',transact_date) as transact_date
  ,decode(grouping(merchant_no),1,decode(grouping(transact_date),1,'','小計'),merchant_no) as merchant_no
  ,decode(grouping(point_no),1,decode(grouping(merchant_no),1,'','小計'),point_no) as point_no
  ,decode(grouping(terminal_no),1,decode(grouping(point_no),1,'','小計'),terminal_no) as terminal_no
  ,null as psam_no,null as card_type,null as card_type_name,null as card_face_no,null as card_logical_number,null as tran_merchant_name,null as tran_merchant_no
  ,null as app_no,null as transact_name,null as transact_type,sum(mid_transact_value) as mid_transact_value,sum(mid_voucher_value) as mid_voucher_value
  ,null as transact_time,null as account_date,null customer_id
  from
  (
       Select to_char(v1.TRANSACT_TIME-4/24,'yyyy-mm-dd') as transact_date,v1.merchant_no,substr(v1.POINT_NO,0,2) point_no,substr(v1.POINT_NO,3,2)  terminal_no,v1.psam_no,
       v1.card_type,v1.card_type_name,v1.card_face_no,v1.card_logical_number,v1.tran_merchant_name,v1.tran_merchant_no,
       v1.app_no,v1.transact_name,v1.transact_type,v1.mid_transact_value,v1.mid_voucher_value,v1.transact_time,v1.account_date,v1.customer_id
       ,1 as order_no
       From v_card_tran_query v1
       where
      ('-1'=v_card_face_no or card_face_no=v_card_face_no)
      and  ('-1'=v_card_logical_number or card_logical_number=v_card_logical_number)      
      and  ('-1'=v_merchant_no or merchant_no=v_merchant_no)
      and  ('-1'=v_point_no or substr(v1.POINT_NO,0,2)=v_point_no)
      and  ('-1'=v_terminal_no or substr(v1.POINT_NO,3,2)=v_terminal_no)
      and  ('-1'=v_card_type or card_type=v_card_type)      
      and v1.account_date between v_account_date1 and v_account_date2
      and v1.transact_time between v_transact_time1 and v_transact_time2
      and
      ( v1.partition_field='00'
         or exists
        (
        select * from sys_partition a where
        a.start_date  between v_account_date1 and v_account_date2
        or a.end_date  between v_account_date1 and v_account_date2
        or v_account_date1 between a.start_date and a.end_date
        or v_account_date2 between a.start_date and a.end_date
        )
      )
  ) v1
  group by rollup(transact_date,merchant_no,point_no,terminal_no)
  having terminal_no is null
  order by transact_date desc,merchant_no asc,point_no asc ,terminal_no asc ;
End transact_data_query1;
End Acc_Card1;