SQL> SELECT OWNER, MAX(LTRIM(SYS_CONNECT_BY_PATH(OBJECT_NAME, ’,’), ’,’)) OBJECT_NAME, 2 MAX(LTRIM(SYS_CONNECT_BY_PATH(SUBOBJECT_NAME, ’,’), ’,’)) SUBOBJECT_NAME, 3 MAX(LTRIM(SYS_CONNECT_BY_PATH(OBJECT_ID, ’,’), ’,’)) OBJECT_ID 4 FROM 5 ( 6 SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, 7 ROW_NUMBER() OVER(PARTITION BY OWNER ORDER BY OBJECT_NAME) RN 8 FROM T 9 WHERE OBJECT_ID BETWEEN 30014 AND 30017 10 ) 11 START WITH RN = 1 12 CONNECT BY PRIOR RN + 1 = RN 13 AND PRIOR OWNER = OWNER 14 GROUP BY OWNER; OWNER OBJECT_NAME SUBOBJECT_NAM OBJECT_ID -------- ---------------------------------------------------- ------------- --------------- OLAPSYS ALL$OLAP1_CUBES,ALL$OLAPMR_DIM_LEVELS_KEYMAPS 30016,30017 PUBLIC CWM2_OLAP_AW_ACCESS,CWM2_OLAP_INSTALLER 30014,30015 如果无法聚集函数函数无法代替，可以使用下面的办法： SQL> SELECT A.OWNER, A.OBJECT_NAME, A.SUBOBJECT_NAME, B.OBJECT_ID 2 FROM 3 ( 4 SELECT OWNER, F_LINK(OBJECT_NAME) OBJECT_NAME, 5 F_LINK(SUBOBJECT_NAME) SUBOBJECT_NAME 6 FROM T 7 WHERE OBJECT_ID BETWEEN 30014 AND 30017 8 GROUP BY OWNER 9 ) A, 10 ( 11 SELECT OWNER, F_LINK(OBJECT_ID) OBJECT_ID 12 FROM T 13 WHERE OBJECT_ID BETWEEN 30014 AND 30017 14 GROUP BY OWNER 15 ) B 16 WHERE A.OWNER = B.OWNER; OWNER OBJECT_NAME SUBOBJECT_NAM OBJECT_ID -------- ---------------------------------------------------- ------------- --------------- OLAPSYS ALL$OLAP1_CUBES,ALL$OLAPMR_DIM_LEVELS_KEYMAPS 30016,30017 PUBLIC CWM2_OLAP_AW_ACCESS,CWM2_OLAP_INSTALLER 30014,30015 只要避免在同一个子查询中自定义聚集函数不要超过限定数量就可以了。
