创建测试用的环境
create table agent(
agent_id number(10) not null primary key,
agent_name varchar2(30)
)
/
create table product(
pro_id number(10) not null primary key ,
name varchar2(30)
)
/
create table bill(
b_id number(10) not null primary key,
agent_id number(10) not null,
pro_id number(10) not null,
sale_count number(10)
)
/
insert into agent
select rownum, '经销处'||rownum
from dual
connect by rownum<10;
insert into product
select rownum, 'product'||to_char(rownum, 'fm0000')
from dual
connect by rownum<10;
insert into bill
select rownum, ceil(dbms_random.value*10), ceil(dbms_random.value*10), trunc(dbms_random.value*100)
from agent, product;
commit;
ok , 环境已经创建, 开始实现统计
在此说明一下, 如果上面的insert语句执行出错, 请确认你使用的oracle版本是否是oracle 9.2或更新的版本, 如果不是, connect by rownum<10这种语句会出错,请自己根据自己的实际情况创建测试数据.
1. 比如 经销处 固定的只要获得 经销处1-5 的统计数据, 则通过下面的sql
select pro_id,
sum(decode(agent_id,1,sale_count,null)) as 经销处1,
sum(decode(agent_id,2,sale_count,null)) as 经销处2,
sum(decode(agent_id,3,sale_count,null)) as 经销处3,
sum(decode(agent_id,4,sale_count,null)) as 经销处4,
sum(decode(agent_id,5,sale_count,null)) as 经销处5
from bill
group by pro_id;
结果如下:
PRO_ID 经销处1 经销处2 经销处3 经销处4 经销处5
----------- ---------- ---------- ---------- ---------- ----------
1 126 7 99 151
2 60 92
3 24 92 47 17
4 150 73 39 18 36
5 55 98 40 136 12
6 83
7 66 93 78
8 57 86
9 43 98 66
10 37 149 25
2. 根据agent表中的实际数据获取上面统计用的sql
上面的sql中有两个地方用到了agent表中的数据, 一个是decode中agent_id的值, 另一个是字段别名中的agent name,
因此需要如下的sql
select 'sum(decode(agent_id,'||agent_id||',sale_count,null)) as '||agent_name as col
from agent
order by agent_id;
SQL> select 'sum(decode(agent_id,'||agent_id||',sale_count,null)) as '||agent_name as col
2 from agent
3 order by agent_id;
COL
--------------------------------------------------------------------------------
sum(decode(agent_id,1,sale_count,null)) as 经销处1
sum(decode(agent_id,2,sale_count,null)) as 经销处2
sum(decode(agent_id,3,sale_count,null)) as 经销处3
sum(decode(agent_id,4,sale_count,null)) as 经销处4
sum(decode(agent_id,5,sale_count,null)) as 经销处5
sum(decode(agent_id,6,sale_count,null)) as 经销处6
sum(decode(agent_id,7,sale_count,null)) as 经销处7
sum(decode(agent_id,8,sale_count,null)) as 经销处8
sum(decode(agent_id,9,sale_count,null)) as 经销处9
sum(decode(agent_id,10,sale_count,null)) as 经销处10
根据返回的结果, 我们就可以轻松的得到用于最后统计的sql
select pro_id,
sum(decode(agent_id,1,sale_count,null)) as 经销处1,
sum(decode(agent_id,2,sale_count,null)) as 经销处2,
sum(decode(agent_id,3,sale_count,null)) as 经销处3,
sum(decode(agent_id,4,sale_count,null)) as 经销处4,
sum(decode(agent_id,5,sale_count,null)) as 经销处5,
sum(decode(agent_id,6,sale_count,null)) as 经销处6,
sum(decode(agent_id,7,sale_count,null)) as 经销处7,
sum(decode(agent_id,8,sale_count,null)) as 经销处8,
sum(decode(agent_id,9,sale_count,null)) as 经销处9,
sum(decode(agent_id,10,sale_count,null)) as 经销处10
from bill
group by pro_id;
另外一种方法：
用第一个SQL生成第二个动态SQL，然后执行查询：
select 'select pro_id'||max(sys_connect_by_path(col, ', '))||' from bill group by pro_id;'
from (
select agent_id, 'sum(decode(agent_id,'||agent_id||',sale_count,null)) as '||agent_name as col
from agent
order by agent_id )
connect by agent_id - 1 = prior agent_id
start with agent_id = 1;
