在 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 顯示相同的錯誤：
我怎樣才能讓它作業？
uj5u.com熱心網友回復：
只需洗掉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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432937.html
標籤：sql 甲骨文 oracle11gr2
上一篇：如何加入具有不同欄位ID的列？