with tmp(name,l) as ( --实际数据
select '李','画画' from dual union all
select '张','吃饭/睡觉' from dual union all
select '王','抽烟/喝酒/打球' from dual
)
select * from tmp;
wan(name,l) as( --想要的查询数据
select '李','画画' from dual union all
select '张','吃饭' from dual union all
select '张','睡觉' from dual union all
select '王','抽烟' from dual union all
select '王','喝酒' from dual union all
select '王','打球' from dual
)
select * from wan;
with tmp(name,l) as ( --实际数据
select '李','画画' from dual union all
select '张','吃饭/睡觉' from dual union all
select '王','抽烟/喝酒/打球' from dual
),
t(name,L,lvl) as
(select name, regexp_substr(L, '[^/]+', 1, 1) L, 2 lvl
from tmp
union all
select x.name, regexp_substr(x.L, '[^/]+', 1, t.lvl), t.lvl + 1
from tmp x, t
where t.name = x.name
and regexp_count(x.L, '[^/]+') >= t.lvl)
select NAME, L from t;
你的数据不在表里,而是sql语句里?