避免 DISTINCT option not allowed for this function 错误 (Oracle 11g)

Question

我在 Oracle DATABASE (Oracle 11g R2) 中运行一些 SQL 时收到此错误消息

代码：

select *
from (
    select 
        v.*
        ,min(cnt_org)over(partition by accountnumber) min_cnt_org
        ,max(cnt_org)over(partition by accountnumber) max_cnt_org
    from (
        select
          accountnumber
          ,org_id
          ,count(org_id)          over(partition by accountnumber) cnt
          ,count(distinct org_id) over(partition by accountnumber) cnt_distinct
          ,count(*)               over(partition by accountnumber,org_id) cnt_org
          ,listagg(org_id,',')within group(order by org_id)
             over(partition by accountnumber)
               as orgs
          ,listagg(distinct org_id,',')within group(order by org_id) 
             over(partition by accountnumber)
               as orgs_distinct
        from mytable
        ) v
    ) v2
where cnt_distinct<>3
or min_cnt_org!=max_cnt_org;

SQL FIDDLE 显示相同的错误： https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=111c3566da71bc6205502bbdf9d3a992

<图片src="https://i.sstatic.net/xw7nW.png" alt="在此处输入图像描述">

我怎样才能让它工作？

Answer 1

只需删除 listagg(distinct...: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=bd47b06a2d6218529cb6a6d0fa6bd678

select *
from (
    select 
        v.*
        ,min(cnt_org)over(partition by accountnumber) min_cnt_org
        ,max(cnt_org)over(partition by accountnumber) max_cnt_org
    from (
        select
          accountnumber
          ,org_id
          ,count(org_id)          over(partition by accountnumber) cnt
          ,count(distinct org_id) over(partition by accountnumber) cnt_distinct
          ,count(*)               over(partition by accountnumber,org_id) cnt_org
          ,listagg(org_id,',')within group(order by org_id)
             over(partition by accountnumber)
               as orgs
        from mytable
        ) v
    ) v2
where cnt_distinct<>3
or min_cnt_org!=max_cnt_org;