1、
ID
1
2
3
5
6
7
8
10
11
12
15
表名tt，用sql找出ID列中不连续的ID，例如其中没有的4：
--创建表及数据 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)
2、
将录入不规范的房间信息整理成规范格式
不规范表（多个房间用逗号分割）
| ID | ROOM |
| 1 | 101,102 |
| 2 | 201,202,203 |
| 3 | 301 |
| ....... |
规范表
| ID | ROOM |
| 1 | 101 |
| 1 | 102 |
| 2 | 201 |
| 2 | 202 |
| 2 | 203 |
| 3 | 301 |
| ...... |
--单行单列转多行 --创建表及数据 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,‘301‘ 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;