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

How to create a temporary / dynamic / virtual table when a SQL runs in Oracle?

提问by Samiul Al Hossaini

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 表。

回答by psaraj12

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 小提琴

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;

回答by Gordon Linoff

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

如果您需要获取完整列表，则需要一个cross join带有一些附加逻辑的 ：

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;

回答by Sena M

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

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

回答by Sanders the Softwarer

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

回答by Samiul Al Hossaini

The movie_genretable 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?

你怎么看待这件事？？是否比对所有双表进行联合更有效？