日期:2014-05-17 浏览次数:20775 次
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
SELECT * 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