Oracle SQL合并具有相同ID但标识符混乱的多行 [英] Oracle SQL Merge Multiple rows With Same ID But Out of Order Identifiers

问题描述

我正在尝试创建零件的不同列表以在表中进行分析.该表包含一列零件ID和一列标识符.标识符在同一条目中由管道分隔开，但是不幸的是，这些标识符是乱序的.我不确定这是否可行，但是任何帮助将不胜感激！

I am trying to create a distinct list of parts to do analysis on in a table. The table contains a column of Part IDs and a column of Identifiers. The Identifiers are separated within the same entry by pipes but unfortunately the Identifiers are out of order. I'm not sure if this is possible but any help would be greatly appreciated!

例如(当前ID和标识符均为VARCHAR2)

For example (currently Both ID and Identifiers are VARCHAR2)

ID  Identifiers
1   |1|2|
1   |2|1|
2   |3|A|1|B|
2   |B|1|3|A|
3   |1|3|2|
3   |1|5|
3   |2|1|3|
4   |AA|BB|1|3A|
4   |1|3A|AA|BB|

我需要查询返回

ID  Identifiers
1   |1|2|
2   |3|A|1|B|
3   |1|5|
3   |1|3|2|
4   |1|AA|BB|3A|

只要标识符中的所有内容都相同，标识符的顺序将无关紧要.例如，| 1 | 5 |或| 5 | 1 |没关系，但我需要查看两个条目| 1 | 5 |和| 1 | 3 | 2.我最初的想法是将标识符分开创建到不同的列中，然后按字母顺序串联回到一列中，但是我不确定...预先感谢！

It does not matter what specific order the identifiers are ordered in as long as all contents within that identifier are the same. For example, |1|5| or |5|1| doesn't matter but I need to see both entries |1|5| and |1|3|2. My original thought was to create separate out the identifiers into separate columns and then alphabetically concatenate back into one column but i'm not sure...thanks in advance!

推荐答案

这样的事情(假设输入表中没有重复的行-如果存在，则需要对解决方案进行一些修改).

Something like this (assuming there are no duplicate rows in the input table - if there are, the solution needs to be modified a bit).

在解决方案中，我构建了用于测试的test_table(它不是解决方案的一部分)，并且在WITH子句中构建了另一个分解式子查询.这在Oracle 11及更高版本中有效.对于早期版本的Oracle，需要将定义为prep的子查询作为子查询移动到最终查询中.

In the solution I build the test_table for testing (it is not part of the solution), and I build another factored subquery in the WITH clause. This works in Oracle 11 and above. For earlier versions of Oracle, the subquery defined as prep needs to be moved as a subquery within the final query instead.

with
     test_table ( id, identifiers ) as (
       select '1', '|1|2|'        from dual union all
       select '1', '|2|1|'        from dual union all
       select '2', '|3|A|1|B|'    from dual union all
       select '2', '|B|1|3|A|'    from dual union all
       select '3', '|1|3|2|'      from dual union all
       select '3', '|1|5|'        from dual union all
       select '3', '|2|1|3|'      from dual union all
       select '4', '|AA|BB|1|3A|' from dual union all
       select '4', '|1|3A|AA|BB|' from dual
     ),
     prep ( id, identifiers, token ) as (
       select id, identifiers, regexp_substr(identifiers, '[^|]+', 1, level)
       from   test_table
       connect by level <= regexp_count(identifiers, '\|') - 1
           and prior identifiers = identifiers
           and prior sys_guid() is not null
     )
select distinct id, 
       '|' || listagg(token, '|') within group (order by token) || '|'
                                                as identifiers
from   prep
group by id, identifiers
order by id, identifiers    --  ORDER BY is optional
;

输出:

ID  IDENTIFIERS
--- --------------------
1   |1|2|
2   |1|3|A|B|
3   |1|2|3|
3   |1|5|
4   |1|3A|AA|BB|

5 rows selected.

这篇关于Oracle SQL合并具有相同ID但标识符混乱的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！