关于统计和页面显示问题

来源：互联网发布：胡歌演技知乎编辑：程序博客网时间：2024/04/30 12:14

前两天，组长给我了一个模块，让我按条件统计一下一个表中的数据，并按条件显示出来，听着没有什么，再说我以前所说也做过类似的东西，不过仔细看了一下给的这个表，发现以前的东西不够用了。

现在我把这个表中的主要字段给出：

表名：record

序号

字段名

字段类型

中文含义

说明

record_id

VARCHAR2 (32)

记录编号

primary key

cycle_no

VARCHAR2 (32)

事项编号，取值只能取右边的几个，我只给出了数值，至于具体代表什么含义，就不写了

001

002

003

004

005

006

record_type

NUMBER(2)

处理类型

10/20/30/40

受理/审查/决定/告知 DEFAULT -1

record_result

NUMBER(1)

处理结果

0/1 成功/失败 default -1(未操作)

accept_op

VARCHAR2(32)

受理人

与user表关联，user_id

accept_date

DATE

受理时间

check_op

VARCHAR2(32)

审查人

与user表关联，user_id

check_date

DATE

审查时间

choose_op

VARCHAR2(32)

决定人

与user表关联，user_id

choose_date

DATE

决定时间

apprise_op

VARCHAR2(32)

告知人

与user表关联，user_id

apprise_date

DATE

告知时间

表User，我们主要用user_id,user_name，这个就不用说了

统计的条件是

统计时间段： start_date end_date]

统计事项： cycle_no (check_box默认都选中)

统计分类：年、月、日 stat_type (统计报表不同、默认日)

是否按人员分类：是、否 is_user (统计报表不同、默认否)

要我给写出统计结果，页面大致如下

事项

统计时间

001

002

……其它项

处理人

受理

审查

决定

告知

受理

审查

决定

告知

2007-10-20

陈**

……其它人员

……

按日期排列

陈**

……其它人员

合计

陈**

此列合计

……其它人员

总计

陈**

四列合计

……其它人员

合计

所有人员四列合计

注：

受理统计时：record_type > 受理、或者record_type = 受理且受理成功(record_result=0)的数据，

审查统计时：record_type > 审查、或者record_type = 审查且审查成功(record_result=0)的数据，

决定统计时：record_type > 决定、或者record_type = 决定且决定成功(record_result=0)的数据，

告知统计时： record_type = 告知且告知成功(record_result=1)的数据的

即如果一行的record_type 是告知时，说明该行已经进行了受理，审查和决定

我在网上查了一下，仅发现以下东西对我比较有参考价值

1） 使用CUBE和ROLLUP对数据进行汇总，SQL语言中的ROLLUP和CUBE命令提供了一个非常有用的工具，可以让您快速深入地获取数据的各种内在性质。ROLLUP和CUBE是SQL的扩展命令，可以在SQL Server 6.5(及以上版本)和Oracle 8i(及以上版本)中使用。具体见本博客的《使用CUBE和ROLLUP对数据进行汇总》篇

2） 使用decode /group by/ order by/sign/sum来实现不同报表的生成，具体见本博客的《SQL行列转换实战》篇

不过我没有想到的是，在Oracle中，对于Cube和Rollup使用，是group by cube(deal_date,deal_op)，而不是SQL Server中的GROUP BY deal_date,deal_op WITH Cube/Rollup

最后我仔细考虑了一下，把这个统计页面的显示分成了以下几步：

1、 建立视图：视图中有五项，分别为处理时间，处理人，事项编号，处理类型，处理数量

注：

1）步中，对于record_type的处理，是直接设置为对应的值，这样的话在以后查询中，不用再加

RECORD_TYPE>record_type OR (RECORD_TYPE=record_type and RECORD_RESULT=0)

的判断，直接用record_type=value就可以了

2）在该步中，对日期用了trunc(accept_date,'DD')，将数据库中的日期取到天项，这样是为了按日，月，年统计时方便

原SQL代码如下：

create or replace view v_censorial_report(deal_date,deal_op,cycle_no,record_type,type_num)

select trunc(accept_date,'DD'),accept_op,cycle_no,10 as record_type,count(record_id)

from t_censorial_cycle_record

where (RECORD_TYPE > 10 OR (RECORD_TYPE=10 and RECORD_RESULT=0)) and accept_op is not null

group by trunc(accept_date,'DD'),accept_op,cycle_no

union

select trunc(check_date,'DD'),check_op,cycle_no,20 as record_type,count(record_id)

from t_censorial_cycle_record

where (RECORD_TYPE>20 OR (RECORD_TYPE=20 and RECORD_RESULT=0)) and check_op is not null

group by trunc(check_date,'DD'),check_op,cycle_no

union

select trunc(choose_date,'DD'),choose_op,cycle_no,30 as record_type,count(record_id)

from t_censorial_cycle_record

where (

RECORD_TYPE>30 OR (RECORD_TYPE=30 and RECORD_RESULT=0)) and choose_op is not null

group by trunc(choose_date,'DD'),choose_op,cycle_no

union

select trunc(apprise_date,'DD'),apprise_op,cycle_no,40 as record_type,count(record_id)

from t_censorial_cycle_record

where (RECORD_TYPE>40 OR (RECORD_TYPE=40 and RECORD_RESULT=0)) and apprise_op is not null

group by trunc(apprise_date,'DD'),apprise_op,cycle_no

2、 得到censorial_report_index.jsp中用户所选择的参数

（1） start_date 开始时间，该项不会为空

（2） end_date 结束时间，该项不会为空

（3） 事项编号，从cycleNo1～cycleNo6

（4） stat_type 分类的类型（year/month/day）

（5） issuer是否按人员分类（true/false）

3、 分析统计条件，判断结果表censorial_report_stat.jsp

注：

（1）对于按何种方式统计，可以select语句中的deal_date进行修改

--按月： to_char(deal_date,'YYYYMM')

--按年： to_char(deal_date,'YYYY')

（2）对于事项编号，可以在select后的语句中，将所选中的列取出，在判断时，只需在下边的sql语句中加上编号就可以了。

cycle_no = '001' or cycle_no = '002' or cycle_no = '003' or cycle_no = '004' or cycle_no = '005' or cycle_no = '006'

1）当isuser为true，stat_type 为day时

事项

统计时间

001

002

……其它项

处理人

受理

审查

决定

告知

受理

审查

决定

告知

2007-10-20

陈**

……其它人员

……

按日期排列

陈**

……其它人员

合计

陈**

此列合计

……其它人员

总计

陈**

四列合计

……其它人员

合计

所有人员四列合计

有上图可知，该项需要得到

对于处理人一项，可用处理人id，然后得到处理人姓名即可

统计时间一列，需要得到每列与右边列的对应关系，即一列跨越多行，需要分别一个相应的n值

对于除总计行外的其余行，可用比较时间得出n

对于总计行，n就是总计行结果集的记录数量＋1

除总计行外，其余行可以用一条sql全部查询出来，结构如下

统计时间

处理人

001

002

……其它项

受理

审查

决定

告知

受理

审查

决定

告知

2007-10-20

11433

2007-10-20

11853

。。

。。。

2007-10-21

11433

2007-10-21

11853

。。。

11433

11853

186

代码如下

select deal_date,deal_op,

sum(case when (record_type = 10 and cycle_no = '001') then type_num else 0 end) ZBRZXTJBA_ACCEPT_NUM,

sum(case when (record_type = 20 and cycle_no = '001') then type_num else 0 end) ZBRZXTJBA_CHECK_NUM,

sum(case when (record_type = 30 and cycle_no = '001') then type_num else 0 end) ZBRZXTJBA_CHOOSE_NUM,

sum(case when (record_type = 40 and cycle_no = '001') then type_num else 0 end) ZBRZXTJBA_APPRISE_NUM,

sum(case when (record_type = 10 and cycle_no = '002') then type_num else 0 end) ZBWJBA_ACCEPT_NUM,

sum(case when (record_type = 20 and cycle_no = '002') then type_num else 0 end) ZBWJBA_CHECK_NUM,

sum(case when (record_type = 30 and cycle_no = '002') then type_num else 0 end) ZBWJBA_CHOOSE_NUM,

sum(case when (record_type = 40 and cycle_no = '002') then type_num else 0 end) ZBWJBA_ACCEPT_NUM,

sum(case when (record_type = 10 and cycle_no = '003') then type_num else 0 end) ZGYSWJBA_ACCEPT_NUM,

sum(case when (record_type = 20 and cycle_no = '003') then type_num else 0 end) ZGYSWJBA_CHECK_NUM,

sum(case when (record_type = 30 and cycle_no = '003') then type_num else 0 end) ZGYSWJBA_CHOOSE_NUM,

sum(case when (record_type = 40 and cycle_no = '003') then type_num else 0 end) ZGYSWJBA_APPRISE_NUM,

sum(case when (record_type = 10 and cycle_no = '004') then type_num else 0 end) ZBFSCB_ACCEPT_NUM,

sum(case when (record_type = 20 and cycle_no = '004') then type_num else 0 end) ZBFSCB_CHECK_NUM,

sum(case when (record_type = 30 and cycle_no = '004') then type_num else 0 end) ZBFSCB_CHOOSE_NUM,

sum(case when (record_type = 40 and cycle_no = '004') then type_num else 0 end) ZBFSCB_APPRISE_NUM,

sum(case when (record_type = 10 and cycle_no = '005') then type_num else 0 end) HTBA_ACCEPT_NUM,

sum(case when (record_type = 20 and cycle_no = '005') then type_num else 0 end) HTBA_CHECK_NUM,

sum(case when (record_type = 30 and cycle_no = '005') then type_num else 0 end) HTBA_CHOOSE_NUM,

sum(case when (record_type = 40 and cycle_no = '005') then type_num else 0 end) HTBA_APPRISE_NUM,

sum(case when (record_type = 10 and cycle_no = '006') then type_num else 0 end) ZBQKSMBG_ACCEPT_NUM,

sum(case when (record_type = 20 and cycle_no = '006') then type_num else 0 end) ZBQKSMBG_CHECK_NUM,

sum(case when (record_type = 30 and cycle_no = '006') then type_num else 0 end) ZBQKSMBG_CHOOSE_NUM,

sum(case when (record_type = 40 and cycle_no = '006') then type_num else 0 end) ZBQKSMBG_APPRISE_NUM

from v_censorial_report

where deal_date >= TO_DATE('2007-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

and deal_date <= TO_DATE('2007-10-31 23:59:59','YYYY-MM-DD HH24:MI:SS')

and(cycle_no = '001' or cycle_no = '002' or cycle_no = '003' or

cycle_no = '004' or cycle_no = '005' or cycle_no = '006')

group by cube(deal_date,deal_op)

对于总计行，结构如下

统计时间

处理人

001

002

……其它项

110

11853

11433

select deal_op,

sum(case when (cycle_no = '001') then type_num else 0 end) ZBRZXTJBA_NUM,

sum(case when (cycle_no = '002') then type_num else 0 end) ZBWJBA_NUM ,

sum(case when (cycle_no = '003') then type_num else 0 end) ZGYSWJBA_NUM,

sum(case when (cycle_no = '004') then type_num else 0 end) ZBFSCB_NUM,

sum(case when (cycle_no = '005') then type_num else 0 end) HTBA_NUM,

sum(case when (cycle_no = '006') then type_num else 0 end) ZBQKSMBG_NUM

from v_censorial_report

where deal_date >= TO_DATE('2007-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')

and deal_date <= TO_DATE('2007-10-31 23:59:59','YYYY-MM-DD HH24:MI:SS')

and(cycle_no = '001' or cycle_no = '002' or cycle_no = '003' or

cycle_no = '004' or cycle_no = '005' or cycle_no = '006')

group by cube(deal_op)

2）当isuser为false，stat_type 为day时

事项

统计时间

招标方式抄报

招标人自行招标文件备案

……其它申报项

受理

审查

决定

告知

受理

审查

决定

告知

2007-10-20

……

按日期排列

合计

此列合计

总计

四列合计

在按日排序的情况下，除总计行外，其余行可以用一条sql全部查询出来，如下

select deal_date,