Oracle SQL 数据获取顺序 [英] Oracle SQL data fetch order

问题描述

我正在尝试实现此布局.我没有共享查询，因为它正在工作，我只是想知道使用哪些函数来实现格式2".一开始我在做 UNION ALL ，这就是我得到格式1"的方式.从多个表中获取数据.我试图在没有 PLSQL 的情况下实现这一目标.谢谢！

I'm trying to achieve this Layout. I'm not sharing the query because it's working, I'm just wondering what functions to use in order to achieve format '2'. In the beginning I was doing UNION ALL , that's how I got format '1'. The data is fetched from multiple tables. I'm trying to achieve this without PLSQL. Thank you !

我是如何得到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"

How I want to transform it '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 在 Classic Reports 中内置了对此类格式的支持 - 它称为中断格式.请按照以下步骤了解其工作原理:

APEX has built-in support for this type of formatting in Classic Reports - it's called Break Formatting. Follow these steps to get an idea of how it works:

使用以下查询创建经典报告:

Create a Classic Report with the following query:

select job,
  ename,
  sal
from emp
order by job, sal

进入报告选项并将Break Columns(在Break Formatting 下)设置为First Column.然后，深入 SAL 列并启用 Sum 设置.

Go into the report options and set Break Columns (under Break Formatting) to First Column. Then, drill into the SAL column and enable the Sum setting.

这应该给你这样的东西:

That should give you something like this:

有关详细信息，请参阅 Jeff Eberhard 的这篇博文:https://blog.eberapp.com/ords/f?p=BLOG:READ:::::ARTICLE:6555300346233507&cs=17CDD85DFBD5E33D7BD8F7945B94027CE

See this blog post by Jeff Eberhard for more info: https://blog.eberapp.com/ords/f?p=BLOG:READ:::::ARTICLE:6555300346233507&cs=17CDD85DFBD5E33D7BD8F7945B94027CE

这篇关于Oracle SQL 数据获取顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！