代码之家 › 专栏 › 技术社区 › 2milli

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

normalization oracle-sqldeveloper pivot oracle sql

2milli · 技术社区 · 7 年前

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
来自电影11
其中actorname像''%Christina Ricci%'
按年度asc订购;

1 回复 | 直到 7 年前

Community Egal 4 年前

第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 |

你来运行它一次然后用它把所有的东西都移动到一个标准化的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 |