create table gxg (sid int,var varchar(400));
insert into gxg
select 104,'1a,2a,3a,'
union all
select 105,'1b,2b,3b,'
union all
select 106,'1c,'
union all
select 107,'';
--我想要的结果是:104,1a,1
104,2a,2
104,3a,3
105,1b,1
105,2b,2
105,3b,3
106,1c,1
107,,1
调试欢乐多
FROM (SELECT SID, REGEXP_SUBSTR (var, '[^,]+', 1, d.l) var, l
FROM gxg g,
(SELECT LEVEL l
FROM DUAL
CONNECT BY LEVEL < 10) d
WHERE REGEXP_SUBSTR (var, '[^,]+', 1, d.l) IS NOT NULL
UNION ALL
SELECT SID, NULL var, 1 l
FROM gxg
WHERE var IS NULL)
ORDER BY SID, l