WITH tab AS( SELECT 1 ID,'椅子' NAME,NULL pid FROM dual UNION ALL SELECT 2, '木椅子', 1 FROM dual UNION ALL SELECT 3, '铁椅子', 1 FROM dual UNION ALL SELECT 4, 'xx木椅子', 2 FROM dual UNION ALL SELECT 5, 'yy木椅子', 2 FROM dual UNION ALL SELECT 6, 'xx铁椅子', 3 FROM dual ) SELECT CONNECT_BY_ROOT NAME "ROOT", CONNECT_BY_ISLEAF "ISLEAF", LEVEL, SYS_CONNECT_BY_PATH(NAME, '/') "PATH" FROM TAB START WITH PID IS NULL CONNECT BY PRIOR ID = PID; 应该会给你启发,递归结果,再根据自己需要得出递归结果与list表进行关联统计就行了。。
SELECT 1 ID,'椅子' NAME,NULL pid FROM dual UNION ALL
SELECT 2, '木椅子', 1 FROM dual UNION ALL
SELECT 3, '铁椅子', 1 FROM dual UNION ALL
SELECT 4, 'xx木椅子', 2 FROM dual UNION ALL
SELECT 5, 'yy木椅子', 2 FROM dual UNION ALL
SELECT 6, 'xx铁椅子', 3 FROM dual
)
SELECT CONNECT_BY_ROOT NAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL,
SYS_CONNECT_BY_PATH(NAME, '/') "PATH"
FROM TAB
START WITH PID IS NULL
CONNECT BY PRIOR ID = PID;
应该会给你启发,递归结果,再根据自己需要得出递归结果与list表进行关联统计就行了。。