ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE 下的个性分组查询统计

ORACLE 下的个性分组查询统计

原创 Linux操作系统 作者:xchao263 时间:2009-04-10 19:51:56 0 删除 编辑

项目需要实现一个统计的功能,

需求可以简单的描述成 查询某部门下某工具一年内创建的次数统计,汇总条件的全年的统计,汇总条件的单月数据统计。

其要求输出的结果如下:

部门名称     工具名称          一月   二月     三月    四月   五月    六月    七月     八月    九月    十月    十一月 十二月 合计
管理部        Tool_2004        6        6        6        6        6        6        6        6        6        6        6        6        72
管理部        Tool_2005        6        6        6        6        6        6        6        6        6        6        6        6        72
管理部        Tool_2006        6        6        6        6        6        6        6        6        6        6        6        6        72
技术部        Tool_2010        6        6        6        6        6        6        6        6        6        6        6        6        72
技术部        Tool_2011        6        6        6        6        6        6        6        6        6        6        6        6        72
技术部        Tool_2012        6        6        6        6        6        6        6        6        6        6        6        6        72
销售部        Tool_2007        6        6        6        6        6        6        6        6        6        6        6        6        72
销售部        Tool_2008        6        6        6        6        6        6        6        6        6        6        6        6        72
销售部        Tool_2009        6        6        6        6        6        6        6        6        6        6        6        6        72
合计                                   12      12      12      12      12      12      12      12      12      12      12      12      144

我建了如下的表示例上面需求的表结构:
创建语句如下:
-- 部门表
create table temp_dept
(
  id int,
  name varchar(200)
  -- 其它略
);
insert into temp_dept(id,name) values(1000,'软件部');
insert into temp_dept(id,name) values(1002,'管理部');
insert into temp_dept(id,name) values(1003,'销售部');
insert into temp_dept(id,name) values(1004,'技术部');
commit;
-- 工具表
create table temp_tool
(
  id int,
  name varchar(500),
  createdate date,
  fkid_dept int
  -- 其它略
);
insert into temp_tool(id,name,createdate,fkid_dept) values(2001,'Tool_2001',add_months(sysdate,1),1001);
insert into temp_tool(id,name,createdate,fkid_dept) values(2002,'Tool_2002',add_months(sysdate,1),1001);
insert into temp_tool(id,name,createdate,fkid_dept) values(2003,'Tool_2003',add_months(sysdate,1),1001);
insert into temp_tool(id,name,createdate,fkid_dept) values(2004,'Tool_2004',add_months(sysdate,1),1002);
insert into temp_tool(id,name,createdate,fkid_dept) values(2005,'Tool_2005',add_months(sysdate,1),1002);
insert into temp_tool(id,name,createdate,fkid_dept) values(2006,'Tool_2006',add_months(sysdate,3),1002);
insert into temp_tool(id,name,createdate,fkid_dept) values(2007,'Tool_2007',add_months(sysdate,2),1003);
insert into temp_tool(id,name,createdate,fkid_dept) values(2008,'Tool_2008',add_months(sysdate,2),1003);
insert into temp_tool(id,name,createdate,fkid_dept) values(2009,'Tool_2009',add_months(sysdate,1),1003);
insert into temp_tool(id,name,createdate,fkid_dept) values(2010,'Tool_2010',add_months(sysdate,3),1004);
insert into temp_tool(id,name,createdate,fkid_dept) values(2011,'Tool_2011',add_months(sysdate,3),1004);
insert into temp_tool(id,name,createdate,fkid_dept) values(2012,'Tool_2012',add_months(sysdate,4),1004);
-- 同一部门下同名的工具 创建日期不同
insert into temp_tool(id,name,createdate,fkid_dept) values(2013,'Tool_2001',add_months(sysdate,2),1001);
insert into temp_tool(id,name,createdate,fkid_dept) values(2014,'Tool_2002',add_months(sysdate,3),1001);
insert into temp_tool(id,name,createdate,fkid_dept) values(2015,'Tool_2003',add_months(sysdate,4),1001);
insert into temp_tool(id,name,createdate,fkid_dept) values(2016,'Tool_2010',add_months(sysdate,1),1004);
insert into temp_tool(id,name,createdate,fkid_dept) values(2017,'Tool_2011',add_months(sysdate,1),1004);
insert into temp_tool(id,name,createdate,fkid_dept) values(2018,'Tool_2012',add_months(sysdate,1),1004);
commit;

另创建了一个视图,将两个表的数据整合到一个表
create  or replace view view_temp_dept_tool
as
select td.id Dept_Id, td.name Dept_Name, tt.* from temp_dept td inner join temp_tool tt on td.id = tt.fkid_dept;


对于上面的输出结果,其数据是不正确的,下面继续说来。


我目前的实现是,动态拼接查询(汇总)字符串,至于为什么是动态的,因为这个汇总条件可能不确定(如:上面是就是根据部门及工具名称,但可能还会是其它的汇总条件,例如:仅只按工具名称汇总),所以用了动态生成查询字符串,以实现查询。

主存储过程:

create or replace
procedure Proc_Temp_Test is
-- 输入参数
START_DATE_STR VARCHAR2(10);
END_DATE_STR VARCHAR2(10);
-- 局部变量
STRS VARCHAR(2000);
M NUMBER(2,0);
CONT NUMBER(10,0);
DT_START DATE; -- 开始时间
DT_END DATE;-- 结果时间
MY_GROUP_BY VARCHAR2(2000); -- 分组条件串
SINGLE_MONTH_COUNT NUMBER; -- 单个月汇总下的总数
ALL_MONTH_COUNT NUMBER; -- 所有月汇总的总数
TYPE REF_CUR IS REF CURSOR;
MY_CUR REF_CUR;
BEGIN
  M := 1;
  -- 模拟输入参数值
  START_DATE_STR := '2010-01-01';
  END_DATE_STR := '2010-12-30';
  SINGLE_MONTH_COUNT := 0; 
  DT_START := TO_DATE(START_DATE_STR,'yyyy-MM-dd');
  DT_END := TO_DATE(END_DATE_STR,'yyyy-MM-dd');
  -- 不同的汇总类型 此处的查询列是不一样的
  -- 以下以 器具名称及部门名称 示例
  MY_GROUP_BY := 'tt.dept_name, tt.name ';
  STRS := 'select tt.dept_name 部门名称,tt.name 器具名称';
 
 /* 此方式下的第个汇总下 每个列(月)数据都是一样的 */
  -- 将数字月转换成能显示的月分串 1->一月..12->十二月。
  WHILE M <= 12 LOOP
    -- 根据分组条件、月分 返回该月的数据的总数
    CONT := FUN_GET_MT_CHECK_MONTH_DATA(MY_GROUP_BY, TO_DATE('2009-'||TO_CHAR(M)||'-01','yyyy-MM-dd'));   
    STRS := STRS || ',''' || TO_CHAR(CONT) ||''' ' || FUN_GET_MONTHCHAR(M);   
    --STRS := STRS || ',TO_CHAR(FUN_GET_MT_CHECK_MONTH_DATA(vmi.availabledate)) ' ||  FUN_GET_MONTHCHAR(M);
    M := M + 1;
    SINGLE_MONTH_COUNT := SINGLE_MONTH_COUNT + CONT;
  END LOOP;
 
  -- 最后的分类合计
  STRS := STRS || ',''' || TO_CHAR(SINGLE_MONTH_COUNT) || ''' 合计'; 
  -- 拼接表查询
  STRS := STRS || ' from view_temp_dept_tool tt ';
  STRS := STRS || ' where  1=1 '
  || ' and to_date(to_char(tt.createdate,''yyyy-MM-dd''),''yyyy-MM-dd'') >= to_date('''|| to_char(DT_START, 'yyyy-MM-dd') || ''',''yyyy-MM-dd'') '
  --|| ' and tt.createdate <= add_months(to_date(''2009-04-30'',''yyyy-MM-dd''), 12)'
  || ' and to_date(to_char(tt.createdate,''yyyy-MM-dd''),''yyyy-MM-dd'') <= to_date('''|| to_char(DT_END, 'yyyy-MM-dd') || ''',''yyyy-MM-dd'') '
  || ' group by ' || MY_GROUP_BY;
 
  -- 最后的月数据统计行
  STRS := STRS || 'UNION SELECT '''', '''''; 
  M := 1;
  -- 添加与月分匹配的列 (数据)
  WHILE M <= 12 LOOP
    STRS := STRS || ','''' ';
    M := M + 1;
  END LOOP;
  -- 合计列
  STRS := STRS || ','''''; 
  STRS := STRS || ' FROM DUAL';
 
  /***   换拼接数据的方法实现    */
END;

其中FUN_GET_MONTHCHAR函数是自己写的,主要是将数字月转成汉字的,因为纯数字的不能作为列别名(也许是我的方法不对),没什么可说的!

FUN_GET_MT_CHECK_MONTH_DATA需要说说,接收两个参数,一个汇总条件(GROUP BY 后面的那串,我称之为汇总条件,也找不到就个好的定义,就这样描述着吧,希望能理解),一个查询的月份,

实现代码如下:

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21280726/viewspace-588619/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-04-10

  • 博文量
    2
  • 访问量
    3566