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

I have some data that has to be measured which are not in any table. I can not insert it to a table nor I can create any table and insert these data. So I used dual like the following to get that table. I used this to join with other tables.

我有一些必须测量的数据，这些数据不在任何表格中。我无法将其插入表中，也无法创建任何表并插入这些数据。所以我使用了以下两个来获取该表。我用它来加入其他表。

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
;

So that I get the result -

这样我就得到了结果 -

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

My question is, is there any better way to do this? Any suggestion will be a life saver.

我的问题是，有没有更好的方法来做到这一点？任何建议都将是一个救生员。

An example -

一个例子 -

Lets say we have a 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);

And we need a result like this -

我们需要这样的结果 -

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

Here, we DO NOT have any movie_genre table.

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

5 个解决方案

#1

You can pass the genre as string in the order you want and use regular expression to generate the movie_genre table.The sql fiddle here

你可以按照你想要的顺序将类型作为字符串传递，并使用正则表达式生成movie_genre表.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;

#2

If you need to get the full list, you want a cross join, with some additional logic:

如果您需要获取完整列表，则需要交叉连接，并具有一些额外的逻辑：

with movie_genre as (
      select '10' as "id", 'action' as "genre" from dual union all
      select '20' as "id", 'horror' as "genre" from dual union all
      select '30' as "id", 'comedy' as "genre" from dual union all
      select '40' as "id", 'adventure' as "genre" from dual union all
      select '50' as "id", 'drama' as "genre" from dual union all
      select '60' as "id", 'mystery' as "genre" from dual union all
      select '70' as "id", 'musical' as "genre" from dual
     )
select m.name, mg.genre,
       (case when m.genre_id = mg.id then 'yes' else 'no' end) as IsInGenre
from movies m cross join
     movie_genres mg;

#3

Not sure if this works for Oracle, but for SQL Server there is a neater implementation.

不确定这是否适用于Oracle，但对于SQL Server，有一个更简洁的实现。

Example: SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);

示例：SELECT a，b FROM（VALUES（1,2），（3,4），（5,6），（7,8），（9,10））AS MyTable（a，b）;

Ref: Section C. Specifying multiple values as a derived table in a FROM clause

参考：C部分。在FROM子句中将多个值指定为派生表

https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql

#4

Thus you can use DECODE function. Something like this:

因此，您可以使用DECODE函数。像这样的东西：

select 
  m.*,
  decode(m.genre_id, 10, 'action', 20, 'horror', ...) as genre
from 
  movies m

If you wish to generate you strange formed resultset, you can use, say

如果你想生成奇怪的形成结果集，你可以使用，比方说

select
  m.name,
  decode(n.id, 10, 'Action', ...) genre,
  case when m.genre_id = n.id then 'yes' else 'no' end is_in_genre
from
  movies m,
  (select 10 * rownum id from dual connect by level <= 7) n

More common way is to eliminate movies_genre at all:

更常见的方法是完全消除movies_genre：

select
  m.name,
  case when m.genre_id = 10 then 'yes' else 'no' end is_action,
  case when m.genre_id = 20 then 'yes' else 'no' end is_horror,
  ....
from
  movies m
from
  movies m,
  (select 10 * rownum id from dual connect by level <= 7) n

#5

The movie_genre table can also be created by the following -

movie_genre表也可以通过以下方式创建 -

with movie_genre as
(
  select i, genre
  from 
  ( select '1000' id, 'length-of-the-longest-value' genre from dual )
  where id <> 1000
  model
  dimension by (genre)
  measures (id i)
  rules
  (
    i['action'] = 10,
    i['horror'] = 20,
    i['comedy'] = 30,
    i['adventure'] = 40,
    i['drama'] = 50,
    i['mystery'] = 60,
    i['musical'] = 70
  )
  order by i
)
select * from movie_genre;

What do you think about this?? Is it more efficient than doing union on all the dual tables?

你怎么看待这件事？？它比在所有双表上进行联合更有效吗？

#1