SELECT TABLE_NAME,
INDEX_NAME,INDEX_COLUMNS
FROM (
SELECT TABLE_NAME,
INDEX_NAME,
DECODE(LV, MAX(LV) OVER(PARTITION BY INDEX_NAME), INDEX_COLUMNS, '') INDEX_COLUMNS
FROM (SELECT TABLE_NAME,
INDEX_NAME,
LEVEL LV,
LTRIM(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), ',') INDEX_COLUMNS
FROM (SELECT TABLE_NAME,
INDEX_NAME,
COLUMN_NAME,
ROW_NUMBER() OVER(PARTITION BY TABLE_NAME, INDEX_NAME ORDER BY COLUMN_NAME) PNO,
ROW_NUMBER() OVER(PARTITION BY TABLE_NAME, INDEX_NAME ORDER BY COLUMN_NAME) - 1 SNO
FROM USER_IND_COLUMNS)
CONNECT BY PRIOR INDEX_NAME = INDEX_NAME
AND PRIOR TABLE_NAME = TABLE_NAME
AND PRIOR PNO = SNO)
) WHERE INDEX_COLUMNS IS NOT NULL AND TABLE_NAME = UPPER('table_name')
在这个oracle层查询中,最讨厌的问题是如何取出所有的列,如果只是简单的对循环嵌套出来的列取最大值,是得不到相应结果的,只能多层嵌套了。各位过客有好的想法,尽可提出哦,多多讨论!