Answers
解决了，这是CSDN上一位大神给我的解答
SELECT CLR, SUM(CLFS1), SUM(CLFS2), SUM(TOTAL)
FROM (SELECT CLR,
DECODE(T2.FWFS, 1, CN, 0) CLFS1,
DECODE(T2.FWFS, 2, CN, 0) CLFS2,
DECODE(T2.FWFS, 1, CN, 0) + DECODE(T2.FWFS, 2, CN, 0) TOTAL
FROM (SELECT CLR, FWFS, COUNT(*) CN
FROM (SELECT ID,
FWFS,
REGEXP_SUBSTR(CLR, '[^,]+', 1, LEVEL) CLR
FROM T
CONNECT BY LEVEL <= REGEXP_COUNT(CLR, ',') + 1
AND ID = PRIOR ID
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL) T1
GROUP BY T1.CLR, FWFS) T2) T3
GROUP BY T3.CLR
ORDER BY T3.CLR;
REGEXP_COUNT这个函数我的数据库没有，用LENGTH加TRANSLATE代替了
鸭梨大过天
answered 11 years ago