sql - Oracle 11g 如何对来自两个不同列的值进行分组

Question

我有一个 oracle VIEW，其中包含两列中的一些值我想将这些值分组到一个新列中添加连接:

myView : 

---------------------------------
ID  | col 1       | col 2        |
---------------------------------
1   | 1,2,3,4     |V1,V2,V3,V4
2   | 4,5,6,7     |V5,V6,V7,V8

我想创建新 View ，添加一个新列 col 3，如下所示:

 ------------------------------------------------------
    ID  | col 1       | col 2      |col 3
 ------------------------------------------------------
    1   | 1,2,3,4     |V1,V2,V3,V4 |1,V1 2,V2 3,V3 4,V4
    2   | 5,6,7,8     |V5,V6,V7,V8 |5,V5 6,V6 7,V7 8,V8

在此先感谢您的帮助

Answer 1

按照 Matthew 的要求，使用相同的扩展样本数据展开逗号分隔列表的 11gR2 兼容版本:

with input_data ( id, col1, col2 ) as (
SELECT 1   , '1,2,3,4',     'V1,V2,V3,V4' from dual union all
SELECT 2   , '4,5,6,7',     'V5,V6,V7,V8' from dual union all
SELECT 3   , 'A',           'VA,VB,VC,VD' from dual union all
SELECT 4   , 'E,F,G',       'VE' from dual union all
SELECT 5   , 'H,I',         '' from dual union all
SELECT 6   , '',            'J,K' from dual
)
, cte (id, col1, col2, pos, combined_value) as (
  select id, col1, col2, level,
    regexp_substr(col1, '(.*?)(,|$)', 1, level, null, 1)
      ||','|| regexp_substr(col2, '(.*?)(,|$)', 1, level, null, 1)
  from input_data
  connect by id = prior id
  and prior dbms_random.value is not null
  and level <= greatest(nvl(regexp_count(col1, ','), 0),
    nvl(regexp_count(col2, ','), 0)) + 1
)
select id,
       col1,
       col2,
       listagg(combined_value, ' ') within group (order by pos) as col3
from cte
group by id, col1, col2;

        ID COL1    COL2        COL3                          
---------- ------- ----------- ------------------------------
         1 1,2,3,4 V1,V2,V3,V4 1,V1 2,V2 3,V3 4,V4           
         2 4,5,6,7 V5,V6,V7,V8 4,V5 5,V6 6,V7 7,V8           
         3 A       VA,VB,VC,VD A,VA ,VB ,VC ,VD              
         4 E,F,G   VE          E,VE F, G,                    
         5 H,I                 H, I,                         
         6         J,K         ,J ,K                         

额外的 CTE 将适当的列值转换为单独数字的列表，并针对列表中的每个 ID 和位置连接在一起。正如在 Matthew 的回答中，每个 ID 的所有串联值都聚合到一个以空格分隔的字符串中。

---

但是，返回到当前 View 的源代码仍然更简单并且可能更有效 - 假设它本身通过创建 col1 和 col2 值字符串聚合 - 并将您的新查询/ View 基于该原始查询。

在其他 View 之上构建 View 可能会导致性能问题，因为优化器不能始终将谓词传递到正确的点。但是，创建值的聚合列表、拆分它们，然后重新聚合它们只会做比您需要的更多的工作。