前两天,组长给我了一个模块,让我按条件统计一下一个表中的数据,并按条件显示出来,听着没有什么,再说我以前所说也做过类似的东西,不过仔细看了一下给的这个表,发现以前的东西不够用了。
现在我把这个表中的主要字段给出:
表名:record
序号 | 字段名 | 字段类型 | 中文含义 | 说明 |
1 | record_id | VARCHAR2 (32) | 记录编号 | primary key |
2 | cycle_no | VARCHAR2 (32) | 事项编号,取值只能取右边的几个,我只给出了数值,至于具体代表什么含义,就不写了 | 001 002 003 004 005 006 |
3 | record_type | NUMBER(2) | 处理类型 | 10/20/30/40 受理/审查/决定/告知 DEFAULT -1 |
4 | record_result | NUMBER(1) | 处理结果 | 0/1 成功/失败 default -1(未操作) |
5 | accept_op | VARCHAR2(32) | 受理人 | 与user表关联,user_id |
6 | accept_date | DATE | 受理时间 |
|
18 | check_op | VARCHAR2(32) | 审查人 | 与user表关联,user_id |
19 | check_date | DATE | 审查时间 |
|
21 | choose_op | VARCHAR2(32) | 决定人 | 与user表关联,user_id |
22 | choose_date | DATE | 决定时间 |
|
24 | apprise_op | VARCHAR2(32) | 告知人 | 与user表关联,user_id |
25 | 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 | 陈** | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
| |
……其它人员 |
|
|
|
|
|
|
|
|
| ||
…… 按日期排列 | 陈** | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
| |
……其它人员 |
|
| |||||||||
合计 | 陈** | 此列合计 |
|
|
|
|
|
|
|
| |
……其它人员 |
|
|
|
|
|
|
|
|
| ||
总计 | 陈** | 四列合计 |
|
| |||||||
……其它人员 |
|
|
| ||||||||
合计 | 所有人员四列合计 |
|
|
注:
受理统计时: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 是告知时,说明该行已经进行了受理,审查和决定
我在网上查了一下,仅发现以下东西对我比较有参考价值
<!--[if !supportLists]-->1) <!--[endif]-->使用CUBE和ROLLUP对数据进行汇总 ,SQL语言中的ROLLUP和CUBE命令提供了一个非常有用的工具,可以让您快速深入地获取数据的各种内在性质。ROLLUP和CUBE是SQL的扩展命令,可以在SQL Server 6.5(及以上版本)和Oracle 8i(及以上版本)中使用。具体见本博客的《使用CUBE和ROLLUP对数据进行汇总》篇
<!--[if !supportLists]-->2) <!--[endif]-->使用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
最后我仔细考虑了一下,把这个统计页面的显示分成了以下几步:
<!--[if !supportLists]-->1、 <!--[endif]-->建立视图:视图中有五项,分别为处理时间,处理人,事项编号,处理类型,处理数量
注:
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)
as
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
<!--[if !supportLists]-->2、 <!--[endif]-->得到censorial_report_index.jsp中用户所选择的参数
<!--[if !supportLists]-->(1) <!--[endif]-->start_date 开始时间,该项不会为空
<!--[if !supportLists]-->(2) <!--[endif]-->end_date 结束时间,该项不会为空
<!--[if !supportLists]-->(3) <!--[endif]-->事项编号,从cycleNo1~cycleNo6
<!--[if !supportLists]-->(4) <!--[endif]-->stat_type 分类的类型(year/month/day)
<!--[if !supportLists]-->(5) <!--[endif]-->issuer是否按人员分类(true/false)
<!--[if !supportLists]-->3、 <!--[endif]-->分析统计条件,判断结果表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 | 陈** | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
|
……其它人员 |
|
|
|
|
|
|
|
|
| |
…… 按日期排列 | 陈** | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
|
……其它人员 |
|
|
|
|
|
|
|
|
| |
合计 | 陈** | 此列合计 |
|
|
|
|
|
|
|
|
……其它人员 |
|
|
|
|
|
|
|
|
| |
总计 | 陈** | 四列合计 |
|
| ||||||
……其它人员 |
|
|
| |||||||
合计 | 所有人员四列合计 |
|
|
有上图可知,该项需要得到
对于处理人一项,可用处理人id,然后得到处理人姓名即可
统计时间一列,需要得到每列与右边列的对应关系,即一列跨越多行,需要分别一个相应的n值
对于除总计行外的其余行,可用比较时间得出n
对于总计行,n就是总计行结果集的记录数量+1
除总计行外,其余行可以用一条sql全部查询出来,结构如下
统计时间 | 处理人 | 001 | 002 | ……其它项 | ||||||
受理 | 审查 | 决定 | 告知 | 受理 | 审查 | 决定 | 告知 |
| ||
2007-10-20 | 11433 | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
|
2007-10-20 | 11853 |
|
|
|
|
|
|
|
|
|
。。 | 。。。 |
|
|
|
|
|
|
|
|
|
2007-10-21 | 11433 | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
|
2007-10-21 | 11853 |
|
|
|
|
|
|
|
|
|
。。。 | 。。。 |
|
|
|
|
|
|
|
|
|
| 11433 | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
|
| 11853 |
|
|
|
|
|
|
|
|
|
|
| 30 | 60 | 42 | 45 | 45 | 36 | 42 | 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 | ……其它项 |
|
| 60 | 110 |
|
| 11853 | 10 | 50 |
|
| 11433 | 50 | 60 |
|
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 | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
|
…… 按日期排列 | 10 | 20 | 14 | 15 | 15 | 12 | 34 | 62 |
|
合计
| 此列合计 |
|
|
|
|
|
|
|
|
总计 | 四列合计 |
|
|
在按日排序的情况下,除总计行外,其余行可以用一条sql全部查询出来,如下
select deal_date,
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)
对于总计行
select
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)
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')
到现在为止,我们的SQL语句已经写出来了,现在我们该要写显示页面了,对于统计我就不详细说了,我仅大致说一下思路:
我是把统计时间和统计人作为HashMap的key项,把后边的全部作为一个Object插入HashMap的value中,原来我本打算把他们放入一个ArrayList中,但是后来发现,因为随着统计事项(cycle_no)的改变,后边的顺序也会随之而改变,所以我后来又选用了HashMap,这样的话就可以由key直接找到value了,不用担心位置的改变了
不过要注意查询结果中的空值
这篇文章本来该上周发的,可是上周朋友来了,结果就没有总结出来,现在终于出来了,就贴出来,供大家参考
当然,这里我所采用的方法可能不是最好的,仅供大家参考,如果大家有什么好的方法,请给我回复,谢谢!