Oracle string_agg(distinct columnname) 在 pl/sql 中不起作用，仅在 sqldeveloper 中起作用

Question

我正在尝试获取 string_agg 函数，详细信息此处来使用独特的关键字。示例用法：

select string_agg(distinct a.name), a.id from tbl_a a group by a.id

问题是这在 sqldeveloper 中有效，但是当在匿名 pl/sql 块中的应用程序 Express 中运行它时，它拒绝接受 unique 关键字，并且我收到一条错误，指出不允许使用 unique 。为什么它会这样做？有一个简单的解决方法吗？我正在使用 10.2 和 applicationexpress 3.2

编辑：澄清一下，我有兴趣解决这个问题，但如果有另一个我可以使用的 string_agg/group_concat 函数，我愿意接受任何想法。

编辑：我最终希望能够做类似的事情

select string_agg(distinct a.name),string_agg(distinct a.city), a.id from tbl_a a group by a.id

，并且对两个 string_agg 列进行重复数据删除...在子查询中使用不同的值时这是一个问题，因为它没有获得两列的不同值...

Answer 1

-- Subject: string_agg + distinct/unique

-- Function string_agg creates a comma separated list of cursor values
-- Source : http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

-- This function has been renamed to string_agg_unique and modified to return a unique list of values (unsort) 
-- (The merge part has not been modified/supported)


CREATE OR REPLACE TYPE t_string_agg_unique AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg_unique)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg_unique,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg_unique,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg_unique,
                                     ctx2  IN      t_string_agg_unique)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg_unique IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg_unique)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg_unique(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg_unique,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    -- Concatenate string only when not already existing in the list (=unique)
    IF instr ( SELF.g_string||','      ,       ','||value||',' ) = 0 
    THEN
       SELF.g_string := self.g_string || ',' || value;
    END IF  ; 
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg_unique,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg_unique,
                                     ctx2  IN      t_string_agg_unique)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;


END;
/
SHOW ERRORS



CREATE OR REPLACE FUNCTION string_agg_unique (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg_unique;
/
SHOW ERRORS


  -- example
  select string_agg_unique ( fruit ) fruit_list from (
     select * from ( 
        select 'Apple' fruit from dual
        union all
        select 'Cherries' fruit from dual  
        union all
        select 'Apple' fruit from dual  
        union all
        select 'Lemon' fruit from dual  
     )
  ) ; 

水果列表

苹果、樱桃、柠檬

Answer 2

根据此 AskTom 线程。

将不同的操作移动到子查询中应该可以：

select   string_agg(sq.name)
        ,sq.id 
from     (
         select distinct 
                a.name
               ,a.id 
         from   tbl_a a 
         ) sq
group by sq.id