如何消除列中的重复数据 [英] How can I eliminate duplicate data in column

问题描述

我想消除重复并显示一次

I would like to eliminate duplicates and show one time

例如

SELECT 'apple, apple, orange'
FROM dual;

我想展示

apple, orange

另一个例子.

SELECT 'apple, apple, apple, apple,'
FROM dual;

我只想展示

apple

此代码显示

with data as
(
  select 'apple, apple, apple, apple' col from dual
)
select listagg(col, ',') within group(order by 1) col
  from (
        select distinct regexp_substr(col, '[^,]+', 1, level) col
          from data
        connect by level <= regexp_count(col, ',')
       )

推荐答案

类似的方法将消除重复项:

Something like this will eliminate duplicates:

SQL演示

SQL Demo

with temp as
(
    select 1 Name, 'test1' Project, 'apple, apple, orange' Error  from dual
    union all
    select 2, 'test2', 'apple, apple, apple, apple,' from dual
), split as (
    select distinct
      t.name, t.project,
      trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value))  as error
    from 
      temp t,
      table(cast(multiset(select level 
                          from dual connect by  level <= length (regexp_replace(t.error, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
)
SELECT Name, listagg(Error, ',') within group(order by 1) as result 
FROM split
GROUP BY Name

输出

如您所见，您会得到一个NULL，因为多余的逗号,

As you can see you get a NULL because that extra comma ,

这篇关于如何消除列中的重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！