题目如下:
机构表:
create table t_branch(
id number(5),
branch_name varchar2(20)
);插入数据:
insert into t_branch values(1,'广州');
insert into t_branch values(2,'深圳);
insert into t_branch values(3,'南京');
insert into t_branch values(4,'北京');环节表:
create table t_tache(
id number(5),
name varchar2(20)
);insert into t_tache values(1,''环节一');
insert into t_tache values(2,''环节二');
insert into t_tache values(3,''环节三');
insert into t_tache values(4,''环节四');
insert into t_tache values(5,''环节五');
统计表:
create table statistic(
id number(5),
t_branch_id number(5),
count1 number(5),
count2 number(5),
tache_id number(5)
);insert into statistic values(1,1,5,10,1);
insert into statistic values(2,2,2,4,2);
insert into statistic values(3,3,3,6,3);
insert into statistic values(4,1,5,10,4);
insert into statistic values(5,4,5,10,1);
每个机构的环节不一样,有的环节多,有的环节少。现在通过sql语句实现下面的输出:
机构 环节一 环节二 环节三 环节四 环节五
——————————————————————————————————————————————————
数量1|数量2 数量1|数量2 数量1|数量2 数量1|数量2 数量1|数量2
广州 5 | 10 0 | 0 0 | 0 0 | 0 0 | 0
深圳 0 | 0 2 | 2 0 | 0 0 | 0 0 | 0
南京 0 | 0 0 | 0 3 | 6 0 | 0 0 | 0
北京 5 | 10 0 | 0 0 | 0 0 | 0 0 | 0
请问这个该怎么做? 在线等,谢谢了。
主要是我只能查出一个环节对应的数据,多个的话我我就不会了。
机构表:
create table t_branch(
id number(5),
branch_name varchar2(20)
);插入数据:
insert into t_branch values(1,'广州');
insert into t_branch values(2,'深圳);
insert into t_branch values(3,'南京');
insert into t_branch values(4,'北京');环节表:
create table t_tache(
id number(5),
name varchar2(20)
);insert into t_tache values(1,''环节一');
insert into t_tache values(2,''环节二');
insert into t_tache values(3,''环节三');
insert into t_tache values(4,''环节四');
insert into t_tache values(5,''环节五');
统计表:
create table statistic(
id number(5),
t_branch_id number(5),
count1 number(5),
count2 number(5),
tache_id number(5)
);insert into statistic values(1,1,5,10,1);
insert into statistic values(2,2,2,4,2);
insert into statistic values(3,3,3,6,3);
insert into statistic values(4,1,5,10,4);
insert into statistic values(5,4,5,10,1);
每个机构的环节不一样,有的环节多,有的环节少。现在通过sql语句实现下面的输出:
机构 环节一 环节二 环节三 环节四 环节五
——————————————————————————————————————————————————
数量1|数量2 数量1|数量2 数量1|数量2 数量1|数量2 数量1|数量2
广州 5 | 10 0 | 0 0 | 0 0 | 0 0 | 0
深圳 0 | 0 2 | 2 0 | 0 0 | 0 0 | 0
南京 0 | 0 0 | 0 3 | 6 0 | 0 0 | 0
北京 5 | 10 0 | 0 0 | 0 0 | 0 0 | 0
请问这个该怎么做? 在线等,谢谢了。
主要是我只能查出一个环节对应的数据,多个的话我我就不会了。
SELECT a.BRANCH_NAME "机构",
Decode(b.tache_id,1,Nvl(b.count1,0)||' | '||Nvl(b.count2,0),0||' | '||0) "数量1|数量2" ,
Decode(b.tache_id,2,Nvl(b.count1,0)||' | '||Nvl(b.count2,0),0||' | '||0) "数量1|数量2" ,
Decode(b.tache_id,3,Nvl(b.count1,0)||' | '||Nvl(b.count2,0),0||' | '||0) "数量1|数量2" ,
Decode(b.tache_id,4,Nvl(b.count1,0)||' | '||Nvl(b.count2,0),0||' | '||0) "数量1|数量2" ,
Decode(b.tache_id,5,Nvl(b.count1,0)||' | '||Nvl(b.count2,0),0||' | '||0) "数量1|数量2"
FROM t_branch a
left join statistic b ON a.id=b.t_branch_id;
t.地区,
sum(环节一a)||'|'||sum(环节一b) as 环节一,
sum(环节二a)||'|'||sum(环节二b) as 环节二,
sum(环节三a)||'|'||sum(环节三b) as 环节三,
sum(环节四a)||'|'||sum(环节四b) as 环节四,
sum(环节五a)||'|'||sum(环节五b) as 环节五
from (select a.branch_name as 地区,
case when b.id = 1 then sum(c.count1) else 0 end as 环节一a,
case when b.id = 1 then sum(c.count2) else 0 end as 环节一b,
case when b.id = 2 then sum(c.count1) else 0 end as 环节二a,
case when b.id = 2 then sum(c.count2) else 0 end as 环节二b,
case when b.id = 3 then sum(c.count1) else 0 end as 环节三a,
case when b.id = 3 then sum(c.count2) else 0 end as 环节三b,
case when b.id = 4 then sum(c.count1) else 0 end as 环节四a,
case when b.id = 4 then sum(c.count2) else 0 end as 环节四b,
case when b.id = 5 then sum(c.count1) else 0 end as 环节五a,
case when b.id = 5 then sum(c.count2) else 0 end as 环节五b
from t_branch a, t_tache b, statistic c
where a.id = c.t_branch_id
and b.id = c.tache_id
group by a.id, a.branch_name,b.id
order by a.id
) t
group by t.地区 地区 环节一 环节二 环节三 环节四 环节五
1 北京 5|10 0|0 0|0 0|0 0|0
2 深圳 0|0 2|4 0|0 0|0 0|0
3 广州 5|10 0|0 0|0 5|10 0|0
4 南京 0|0 0|0 3|6 0|0 0|0
t.地区,
sum(环节一a)||'|'||sum(环节一b) as 环节一,
sum(环节二a)||'|'||sum(环节二b) as 环节二,
sum(环节三a)||'|'||sum(环节三b) as 环节三,
sum(环节四a)||'|'||sum(环节四b) as 环节四,
sum(环节五a)||'|'||sum(环节五b) as 环节五
from (select a.id,
a.branch_name as 地区,
case when b.id = 1 then sum(c.count1) else 0 end as 环节一a,
case when b.id = 1 then sum(c.count2) else 0 end as 环节一b,
case when b.id = 2 then sum(c.count1) else 0 end as 环节二a,
case when b.id = 2 then sum(c.count2) else 0 end as 环节二b,
case when b.id = 3 then sum(c.count1) else 0 end as 环节三a,
case when b.id = 3 then sum(c.count2) else 0 end as 环节三b,
case when b.id = 4 then sum(c.count1) else 0 end as 环节四a,
case when b.id = 4 then sum(c.count2) else 0 end as 环节四b,
case when b.id = 5 then sum(c.count1) else 0 end as 环节五a,
case when b.id = 5 then sum(c.count2) else 0 end as 环节五b
from t_branch a, t_tache b, statistic c
where a.id = c.t_branch_id
and b.id = c.tache_id
group by a.id, a.branch_name,b.id
order by a.id
) t
group by t.id,t.地区
order by t.id 地区 环节一 环节二 环节三 环节四 环节五
1 广州 5|10 0|0 0|0 5|10 0|0
2 深圳 0|0 2|4 0|0 0|0 0|0
3 南京 0|0 0|0 3|6 0|0 0|0
4 北京 5|10 0|0 0|0 0|0 0|0
这样应该是楼主的本意吧
select
(select branch_name from t_branch where s.t_branch_id=id) "机构",
max(decode(tache_id,1,count1||'|'||count2,'0|0')) "环节一",
max(decode(tache_id,2,count1||'|'||count2,'0|0')) "环节二",
max(decode(tache_id,3,count1||'|'||count2,'0|0')) "环节三",
max(decode(tache_id,4,count1||'|'||count2,'0|0')) "环节四",
max(decode(tache_id,5,count1||'|'||count2,'0|0')) "环节五"
from statistic s
group by t_branch_id;
看出来了,我没环境测试,看你9楼把我SQL运行结果显示出来了,分组求和再decode转换就行了。
不知道楼主是不是我们说的这样啊?看他给的数据结果,我硬是没看出来怎么回事。
呵呵。