Oracle SQL数据获取顺序

阿迪CN

我正在尝试实现此布局。我不共享查询,因为它正在工作,我只是想知道要使用什么函数来实现格式“ 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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章