如何在Oracle表中将逗号分隔值拆分为多行

Question

SELECT year, movietitle, director, actorname 
    FROM films11 
    WHERE actorname like '%Christina Ricci%' 
    order by year asc; 

在ORACLE SQL Developer中从原始数据模式中产生以下内容。

我想，这样的主键成为actor name改造整个表。 （如在第二个表）

这种方式查询

SELECT year, movietitle, director, actorname 
    FROM films11 
    WHERE actorname like '%Christina Ricci%' 
    order by year asc; 

将产生仅搜索项（或者创建一个新的视图，或完全改变数据模式。）（第三表）

Answer 1

第1步：“如何炸毁数据库”

来源：

SQL Fiddle

的Oracle 11g R2架构设置：

查询1：

select * from films11 

Results：

| YEAR | DIRECTOR | MOVIETITLE |  ACTORNAME | 
|------|----------|------------|----------------| 
| 2000 |  dir1 |  title1 |  act1,act2 | 
| 2001 |  dir2 |  title2 | act1,act2,act3 | 
| 2002 |  dir1 |  title3 |   act4 | 

查询2：

select YT.year, YT.movietitle, 
     REPLACE(REGEXP_SUBSTR(YT.actorname||',','.*?,',1,lvl.lvl),',','') AS actorname 
from films11 YT 
join (select level as lvl 
     from dual 
     connect by level <= (select max(regexp_count(actorname,',')+1) from films11) 
    ) lvl on lvl.lvl <= regexp_count(YT.actorname,',')+1 
    order by YT.year, YT.movietitle, actorname 

有了一个很好的笛卡尔积：

Results：

| YEAR | MOVIETITLE | ACTORNAME | 
|------|------------|-----------| 
| 2000 |  title1 |  act1 | 
| 2000 |  title1 |  act2 | 
| 2001 |  title2 |  act1 | 
| 2001 |  title2 |  act2 | 
| 2001 |  title2 |  act3 | 
| 2002 |  title3 |  act4 | 

你运行它ONCE，并使用它的一切移动到一个标准化的DB

---

以下是完整的脚本到你的架构改变的东西更方便......

CREATE TABLE actors(
    id_actor NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
    act_name VARCHAR2(100) 
) 
; 

CREATE TABLE directors(
    id_director NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
    dir_name VARCHAR2(100) 
) 
; 

CREATE TABLE movies(
    id_movie NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
    mov_year NUMBER, 
    mov_name VARCHAR2(100), 
    director_id NUMBER 
) 
; 

CREATE TABLE playedby(
    movie_id NUMBER, 
    actor_id NUMBER 
) 
; 

    INSERT INTO directors (dir_name) 
    SELECT DISTINCT director dir_name 
    FROM films11 
    ; 

    INSERT INTO movies (mov_year, mov_name, director_id) 
    SELECT year mov_year, movietitle mov_name, directors.id_director director_id 
    FROM films11 
    INNER JOIN directors ON directors.dir_name = films11.director 

    ; 

    INSERT INTO actors (act_name) 
    SELECT DISTINCT t.actorname act_name 
    FROM (
     SELECT YT.year, YT.movietitle, 
       REPLACE(REGEXP_SUBSTR(YT.actorname||',','.*?,',1,lvl.lvl),',','') AS actorname 
     FROM films11 YT 
     JOIN (SELECT level AS lvl 
       FROM dual 
       CONNECT BY level <= (SELECT MAX(REGEXP_COUNT(actorname,',')+1) FROM films11) 
      ) lvl ON lvl.lvl <= REGEXP_COUNT(YT.actorname,',')+1 
    ) t 
    ; 

    INSERT INTO playedby (movie_id, actor_id) 
    SELECT movies.id_movie movie_id, actors.id_actor actor_id 
    FROM (
     SELECT YT.year, YT.movietitle, 
       REPLACE(REGEXP_SUBSTR(YT.actorname||',','.*?,',1,lvl.lvl),',','') AS actorname 
     FROM films11 YT 
     JOIN (SELECT level AS lvl 
       FROM dual 
       CONNECT BY level <= (SELECT MAX(REGEXP_COUNT(actorname,',')+1) FROM films11) 
      ) lvl ON lvl.lvl <= REGEXP_COUNT(YT.actorname,',')+1 
    ) t 
    INNER JOIN actors ON t.actorname = actors.act_name 
    INNER JOIN movies ON t.year = movies.mov_year AND t.movietitle = movies.mov_name 

    ; 

这之后你可以做一个选择这样的：

查询3：

SELECT mov_year, mov_name, dir_name, act_name 
FROM movies 
INNER JOIN directors ON directors.id_director = movies.director_id 
INNER JOIN playedby ON movies.id_movie = playedby.movie_id 
INNER JOIN actors ON playedby.actor_id = actors.id_actor 
WHERE act_name like '%act2%' 
order by mov_year asc 

Results：

| MOV_YEAR | MOV_NAME | DIR_NAME | ACT_NAME | 
|----------|----------|----------|----------| 
|  2000 | title1 |  dir1 |  act2 | 
|  2001 | title2 |  dir2 |  act2 |