如何在pl/sql查询函数中设置循环,使每个循环输出的结果放在不同的列中,并且全部放在一个表中。
该函数为:
select t1.aaa, coalesce(t2.bbb_count, 0) bbb_count,
coalesce(t2.ccc_sum, 0) ccc_sum
from (
select distinct aaa
from nrb
) t1
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '50%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t2 on t1.aaa = t2.aaa
order by t1.aaa;
这个函数会给我一个200行3列的表格。我需要做一个循环
“和t.ddd喜欢'50%'”
行,从50到55。我的意思是结果将是200行15列。
Pl/sql 7.0.2无限用户许可
保监处: 9.2
oracle数据库: 11.1.0.6.0企业版
操作系统: win xp
发布于 2012-07-29 13:49:38
select t1.aaa,
coalesce(t2.bbb_count, 0) bbb_50_count, coalesce(t2.ccc_sum, 0) ccc_50_sum,
coalesce(t3.bbb_count, 0) bbb_51_count, coalesce(t3.ccc_sum, 0) ccc_51_sum,
coalesce(t4.bbb_count, 0) bbb_52_count, coalesce(t4.ccc_sum, 0) ccc_52_sum,
coalesce(t5.bbb_count, 0) bbb_53_count, coalesce(t5.ccc_sum, 0) ccc_53_sum,
coalesce(t6.bbb_count, 0) bbb_54_count, coalesce(t6.ccc_sum, 0) ccc_54_sum,
coalesce(t7.bbb_count, 0) bbb_55_count, coalesce(t7.ccc_sum, 0) ccc_55_sum
from (
select distinct aaa
from nrb
) t1
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '50%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t2 on t1.aaa = t2.aaa
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '51%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t3 on t1.aaa = t3.aaa
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '52%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t4 on t1.aaa = t4.aaa
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '53%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t5 on t1.aaa = t5.aaa
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '54%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t6 on t1.aaa = t6.aaa
left join (
select t.aaa, count (t.bbb) bbb_count, sum (t.ccc) ccc_sum
from nrb t
where t.vvv IN ('3','4','5','6','D','E','F')
and t.ddd like '55%'
and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD')
and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD')
group by t.aaa
) t7 on t1.aaa = t7.aaa
order by t1.aaa;
https://stackoverflow.com/questions/11706946
复制相似问题