sql - 在 Oracle 中运行 SQL 时如何创建临时/动态/虚拟表？

Question

我有一些必须测量的数据，这些数据不在任何表格中。我不能将它插入到表中，也不能创建任何表并插入这些数据。所以我像下面这样使用 dual 来获取该表。我用它来连接其他表。

with movie_genre as
(
select '10' as "id", 'action' as "genre" from dual
union select '20' as "id", 'horror' as "genre" from dual
union select '30' as "id", 'comedy' as "genre" from dual
union select '40' as "id", 'adventure' as "genre" from dual
union select '50' as "id", 'drama' as "genre" from dual
union select '60' as "id", 'mystery' as "genre" from dual
union select '70' as "id", 'musical' as "genre" from dual
)
select * from movie_genre
;

这样我就得到了结果-

id  genre
10  action
20  horror
30  comedy
40  adventure
50  drama
60  mystery
70  musical

我的问题是，有没有更好的方法来做到这一点？任何建议都可以挽救生命。

一个例子-

假设我们有一张 table -

create table movies (
id number,
name varchar2(50),
genre_id number
);

insert into movies values (1, 'the hulk', 10);
insert into movies values (2, 'dumb and dumber', 30);
insert into movies values (3, 'frozen', 70);

我们需要这样的结果-

name                genre        is_in_genre
the hulk            action       yes
the hulk            horror       no
the hulk            comedy       no
the hulk            adventure    no
the hulk            drama        no
the hulk            mystery      no
the hulk            musical      no
dumb and dumber     action       no
dumb and dumber     horror       no
dumb and dumber     comedy       yes
dumb and dumber     adventure    no
dumb and dumber     drama        no
dumb and dumber     mystery      no
dumb and dumber     musical      no
frozen              action       no
frozen              horror       no
frozen              comedy       no
frozen              adventure    no
frozen              drama        no
frozen              mystery      no
frozen              musical      yes

在这里，我们没有任何 movie_genre 表。

Answer 1

您可以按所需顺序将流派作为字符串传递，并使用正则表达式生成电影流派表。sql fiddle here

with movie_genre as
( 
 select level * 10 as id, regexp_substr(genre,'[^,]+',1,level) as genre
 from
 (
  select ('action,horror,comedy,adventure,drama,mystery,musical') 
  as genre from dual
  )
  connect by level <=REGEXP_COUNT(genre,'[^,]+')
 )
 select * from movie_genre;