基于这类报表的取数,可能要重复N次,并且每次取数的要求参数都不同,所以建议建一张参考表,字段应该包括
ID 取数序号,
type_name 类型编码
account 类型所含account
flag 统计分组标志
这样可以和源表关联起来,不至于每次都要改写大段的SQL来定义参数.
Create Table restriction (Id Number(10),type_name Varchar2(40),Account Number(10),flag Number(1));
根据报表要求输入参数
Insert Into restriction Values(1,'1',101,1);
Insert Into restriction Values(1,'1',102,1);
Insert Into restriction Values(1,'1',103,1);
Insert Into restriction Values(1,'1.1',101,1);
Insert Into restriction Values(1,'1.1',102,1);
Insert Into restriction Values(1,'1.2',103,1);
Insert Into restriction Values(1,'2',104,1);
Insert Into restriction Values(1,'2',105,1);
Insert Into restriction Values(1,'2.1',104,1);
Insert Into restriction Values(1,'3',106,1);
Insert Into restriction Values(1,'3',107,1);
Insert Into restriction Values(1,'4',108,0);
Insert Into restriction (select 1,'汇总',account,1 from xxta_account_f where account<>108);
设计原理:
1.选取源表中符合参数限制的数据,并将参数account合并成一条,同时统计出以flag,type_name分组的number总数,
将结果集中第一次出现源表中account对应的行数标定为1,防止在下一步统计number总数时重复计算.
2.合并格式达到报表要求,并统计出报表要求number总数,完成报表
Select Distinct type_name||'('||Account||')' type_name,
type_total,to_char(round(type_total/Sum(Case When rn=1 And flag=1 Then num End)over(Partition By flag Order By flag)*100,2))||Case When flag=1 Then '%' Else '非统计项目' End Percent
From
(Select a.type_name,a.flag,b.num,
List(a.Account)over(Partition By a.type_name Order By a.type_name) Account,
Sum(b.num)over(Partition By a.flag,a.type_name Order By a.type_name) type_total,
row_number()over(Partition By b.Account Order By b.Account) rn
From restriction a,xxta_account_f b
Where a.Account=b.Account
And a.Id=1
Order By a.type_name,b.Account
)
Order By type_name;
报表输出如下:
TYPE_NAME TYPE_TOTAL PERCENT
-------------------------------------------------------------------------------- ---------- --------------------------------------------------
1(101,102,103) 6000 21.43%
1.1(101,102) 3000 10.71%
1.2(103) 3000 10.71%
2(104,105) 9000 32.14%
2.1(104) 4000 14.29%
3(106,107) 13000 46.43%
4(108) 8000 非统计项目
汇总(101,103,107,106,105,104,102) 28000 100%
以上为个人见解,供大家参考^_^
[本帖最后由 tomgray 于 2007-12-15 11:07 编辑]