我正在尝试实现此布局。我不共享查询,因为它正在工作,我只是想知道要使用什么函数来实现格式“ 2”。一开始我就是这样做的UNION ALL
,这就是我得到格式'1'的方式。从多个表中获取数据。我正在尝试在没有PLSQL的情况下实现这一目标。谢谢 !
我怎么得到它“ 1”
+-----+----+---+-----+
|col_1|col2|...|col_n|
+-----+----+---+-----+
| A |bla |...+ 3 |
+-----+----+---+-----+
| A |bla1|...| 1 |
+-----+----+---+-----+
| B |asd |...+ 2 |
+-----+----+---+-----+
| B |qwe1|...| 6 |
+-----+----+---+-----+
| B |zxc1|...| 1 |
+-----+----+---+-----+
|Sum:A| |...| 4 |
+-----+----+---+-----+
|Sum:B| |...| 9 |
+-----+----+---+-----+
我想如何将其转换为“ 2”
+-----+----+---+-----+
|col_1|col2|...|col_n|
+-----+----+---+-----+
| A |bla |...+ 3 |
+-----+----+---+-----+
| |bla1|...| 1 |
+-----+----+---+-----+
|Sum:A| |...| 4 |
+-----+----+---+-----+
| B |asd |...+ 2 |
+-----+----+---+-----+
| |qwe1|...| 6 |
+-----+----+---+-----+
| |zxc1|...| 1 |
+-----+----+---+-----+
|Sum:B| |...| 9 |
+-----+----+---+-----+
select
col_1
,col_2
...
col_n
from(
select
kce.name as col_1
,kcp.other_name as col_2
...
,irm.col_n
from tab_1 irm
left join tab_2 kce
on irm.irm_s_id = kce.id
left join tab_3 kcp
on irm.irm_p_id = kcp.id
where irm.customer = :P1_GROUP
order by irm.irm_s_id,irm.irm_p_id
)
union all
select 'Sum '||col_1
,null
...
,sum(col_n)
from(
select
kce.name as col_1
,kcp.other_name as col_2
...
,irm.col_n
from tab_1 irm
left join tab_2 kce
on irm.irm_s_id = kce.id
left join tab_3 kcp
on irm.irm_p_id = kcp.id
where irm.customer = :P1_GROUP
order by irm.irm_s_id,irm.irm_p_id
) group by 'Sum '||col_1
APEX在经典报表中内置了对这种格式设置的支持-称为中断格式。请按照以下步骤了解其工作原理:
使用以下查询创建经典报表:
select job,
ename,
sal
from emp
order by job, sal
进入报告选项,然后将“中断列”(在“中断格式”下)设置为“第一列”。然后,钻入SAL列并启用Sum设置。
有关更多信息,请参阅Jeff Eberhard的博客文章:https : //blog.eberapp.com/ords/f? p = BLOG:READ:::::ARTICLE: 6555300346233507& cs =17CDD85DFBD5E33D7BD8F7945B94027CE
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句