oracle的报表的典型sql语句

[color=blue]实现SQL语句[/color]
[color=darkblue][b]
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'[/color]
[color=darkblue]zxbxiaobo@sina.com pwd:[/color][color=white]xiaobo[/color][color=blue]
看看首项，第二项效果怎么样![/color][/b][color=olive][/color]

[color=olive]创建包,包体,使用存储过程 返回游标集合

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;[/color]