使用oracle中的REGEXP_REPLACE区分CSV值 [英] Distinct of CSV values using REGEXP_REPLACE in oracle

问题描述

我有一个包含下列数据的表格

 列A列B 
 
 1 POW 
 2 POW 
 1 POWPRO 
 1 PRO 
 2 PRO 
 1 PROUTL 
 1 TNEUTL 
 1 UTL 
 1 UTLTNE 
 

我需要输出如下

 输出
 
列A列B 
 
 1,2 POW，POWPRO，PRO，PROUTL，TNEUTL，UTL，UTLTNE 
  

我试过下面的查询。但输出是不同的。

  select dbms_lob.substr（ltrim（REGEXP_REPLACE（REPLACE（
 REPLACE（
 XMLAGG（
 XMLELEMENT（A，COULUMN_A）
 ORDER BY COULUMN_A）.getClobVal（），
'&'A'，'，'），
'< / A& ''），'（[^，] +）（，\ 1）+'，'\1'），
 dbms_lob.substr（ltrex（REGEXP_REPLACE（
 REPLACE $ b XMLAGG（
 XMLELEMENT（A，COULUMN_B）
 ORDER BY COULUMN_B）.getClobVal（），
'&A'，'，'），
' < / A>'，''），'（[^，] +）（，\ 1）+'，'\1'）
  
 列A列B 
 1,2 POW，POWPRO，PROUTL，TNEUTL，UTLTNE 
  
我只想使用regexp_replace 
解决方案
我将开始假设你的实际列名是 A 和 B ，而不是A栏 c $ c>Column B。 
 
 
 
您想要 LIST_AGG （可用）对于Oracle 11g及更高版本）：
  SELECT 
 LISTAGG（A，'，'） ）AS A_VALUES 
 FROM TABLE_NAME; 
  
不要被 WITHIN GROUP 位。这是一个分析函数，它只是Oracle的名称，用于其他DB调用窗口函数。分析/窗口函数的基本思想是它可以访问相邻结果行中的数据，以确定当前行的值。一个简单的例子是一个累积和。
 
 
 
在这种情况下，它的分析/窗口是多余的。我们只是使用它来聚合。我们必须提供 WITHIN GROUP 部分，以防止语法错误。我们告诉它如何排序列表，在这种情况下，它只是我们聚合的列。你可以做更多的分析/窗口函数，但这不是去的地方去这些能力。
 
 
 
事情变得有点复杂，因为你想要 DISTINCT 值：
  SELECT 
 LISTAGG ，'）WITHIN GROUP（ORDER BY A）AS A_VALUES 
 FROM（
 SELECT DISTINCT A 
 FROM TABLE_NAME 
）; 
  
更复杂，因为您需要两个列：
  SELECT * 
 FROM（
 SELECT 
 LISTAGG（A，'，'）WITHIN GROUP（ORDER BY A）AS A_VALUES 
 FROM（
 SELECT DISTINCT A 
 FROM TABLE_NAME 
）
）A_VALS，
（
 SELECT 
 LISTAGG（B，'，'） WITHIN GROUP（ORDER BY B）AS B_VALUES 
 FROM（
 SELECT DISTINCT B 
 FROM TABLE_NAME 
）
）B_VALS 
  
这最后一个给你回来你想要的。它只是从子查询中每个列的不同值创建一个字符串，然后执行完全连接（逗号，因为没有过滤器）将两列放在一起。每个子查询返回单个行，因此您只能在最终结果中获取一行。
 
I have a table with data like below
Column A      Column B

1             POW
2             POW
1             POWPRO
1             PRO
2             PRO
1             PROUTL
1             TNEUTL
1             UTL
1             UTLTNE
And I need output like below
Output

Column A      Column B

1,2           POW,POWPRO,PRO,PROUTL,TNEUTL,UTL,UTLTNE  
I tried below query. But the output is different. 
select dbms_lob.substr( ltrim(REGEXP_REPLACE(REPLACE(
     REPLACE(
       XMLAGG(
         XMLELEMENT("A",COULUMN_A )
           ORDER BY COULUMN_A).getClobVal(),
         '<A>',','),
         '</A>',' '),'([^,]+)(,\1)+', '\1'),
dbms_lob.substr( ltrim(REGEXP_REPLACE(REPLACE(
     REPLACE(
       XMLAGG(
         XMLELEMENT("A",COULUMN_B )
           ORDER BY COULUMN_B).getClobVal(),
         '<A>',','),
         '</A>',' '),'([^,]+)(,\1)+', '\1') from table_name
But the output is 
Column A     Column B
1,2          POW ,POWPRO ,PROUTL ,TNEUTL ,UTLTNE 
I want to use only the regexp_replace to search the pattern. Please help me out. 
解决方案
I'm going to start by assuming that your actual column names are A and B, rather than "Column A" and "Column B". If that assumption is wrong, all you need to do is change the names below.

You want LIST_AGG (available for Oracle 11g and higher):
SELECT
    LISTAGG(A, ',') WITHIN GROUP (ORDER BY A) AS A_VALUES
FROM TABLE_NAME;
Don't be thrown by the WITHIN GROUP bit. This is an "analytic function," which is just Oracle's name for what other DBs call window functions. The basic idea of an analytic/window function is that it lets you access data in neighboring result row to determine a value for the current row. A simple example of what they're good for is a cumulative sum.

In this case, the fact that it's analytic/window is superfluous. We're just using it to aggregate. We have to provide the WITHIN GROUP part, though, to prevent a syntax error. We tell it what to order the list by, which in this case is just the column we're aggregating. You can do a lot more with analytic/window functions, but this isn't the place to go over those capabilities.

Things get a little more complicated since you want the DISTINCT values:
SELECT
    LISTAGG(A, ',') WITHIN GROUP (ORDER BY A) AS A_VALUES
FROM (
    SELECT DISTINCT A
    FROM TABLE_NAME
);
And even more complicated since you want both columns:
SELECT *
FROM (
    SELECT
        LISTAGG(A, ',') WITHIN GROUP (ORDER BY A) AS A_VALUES
    FROM (
        SELECT DISTINCT A
        FROM TABLE_NAME
    )
) A_VALS,
(
    SELECT
        LISTAGG(B, ',') WITHIN GROUP (ORDER BY B) AS B_VALUES
    FROM (
        SELECT DISTINCT B
        FROM TABLE_NAME
    )
) B_VALS
This last one gives you back what you wanted. It just creates a string from the distinct values for each column in a subquery, and then it does a full join (the comma, since there are no filters) to put the two columns together. Each subquery gives back on a single row, so you'll only get one row in the final result.

                        
这篇关于使用oracle中的REGEXP_REPLACE区分CSV值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！