--创建表及数据

CREATE TABLE tt(ID INTEGER);

INSERT INTO tt

SELECT 1 FROM dual

UNION ALL

SELECT 2 FROM dual

UNION ALL

SELECT 3 FROM dual

UNION ALL

SELECT 5 FROM dual

UNION ALL

SELECT 6 FROM dual

UNION ALL

SELECT 7 FROM dual

UNION ALL

SELECT 8 FROM dual

UNION ALL

SELECT 10 FROM dual

UNION ALL

SELECT 11 FROM dual

UNION ALL

SELECT 12 FROM dual

UNION ALL

SELECT 15 FROM dual;

COMMIT;

--用到了connect by level 造数据

WITH IT AS

 (SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(ID) FROM TT))

SELECT A.ID

  FROM IT A

 WHERE NOT EXISTS (SELECT 1 FROM TT B WHERE A.ID = B.ID)

--单行单列转多行

--创建表及数据

create table ttt(id integer,room varchar2(200));

insert into ttt

select 1,'101,102' from dual

union all

select 2,'201,202,203' from dual

union all

select 3,'' from dual;

commit;

SELECT DISTINCT ID,REGEXP_SUBSTR(room, '[^,]+', 1, LEVEL, 'i') AS STR

  FROM ttt

CONNECT BY LEVEL <= LENGTH(room) - LENGTH(REGEXP_REPLACE(room, ',', ''))+1;