oracle不定行列统计

Question

oracle行列统计

Answer 1

with A AS(
select rownum RN from dual 
connect by rownum<=(select max(length(people)-length(replace(people,','))+1) from ZRB)
)
SELECT name,
SUM(DECODE(ROOMNUM,1,1)) 房间1,
SUM(DECODE(ROOMNUM,2,1)) 房间2 ,
SUM(DECODE(ROOMNUM,3,1)) 房间3 ,
SUM(DECODE(ROOMNUM,4,1)) 房间4 ,
SUM(DECODE(ROOMNUM,5,1)) 房间5,
SUM(1) 合计
FROM(
SELECT B.ROOMNUM,REXEXP_SUBSTR(B.people,'[^,]+',1,A.RN) NAME
FROM A,ZRB B
WHERE A.RN<=length(B.people)-length(replace(B.people,','))+1)
group by name

Answer 2

这次房间固定，还有REXEXP_SUBSTR在我这没法用，我这环境是10g，能否给出10g解法

Answer 3

REXEXP_SUBSTR在10g应该就能用了
REXEXP_COUNT需要11g才能用

Answer 4

大哥，我问一句，是不是REGEXP_SUBSTR，而不是REXEXP_SUBSTR

Answer 5

刚发现拼写错了,应该是REGEXP_SUBSTR