在Oracle SQL中构造一个临时表 [英] Constructing a temporary table in Oracle SQL

问题描述

我试图创建一个在两个值之间存储"解码的子表，因为我需要多次使用该解码.假设这些是我的表格:

I am trying to make a sub-table that "stores" a decode between two values, because I need to use that decode multiple times. Let's say these are my tables:

Table Person
Name    Number_name
Jeremy  One
Thomas  Two
Stephen Three

我当前的SQL看起来像这样:

my current SQL looks like this:

SELECT
    decode (number_name,
    'one',1,
    'two',2,
    'three',3,
    'four',4)
    num
    FROM person where name = 'Jeremy'
    and (some other condition)
UNION SELECT
    decode (number_name,
    'one',1,
    'two',2,
    'three',3,
    'four,4)
    num
    FROM Person
    where Name <> "Jeremy"
    and (some other condition)

我想做的是这样的:

SELECT num from my_temp_table where name = "Jeremy" and (some other condition)
union select num from my_temp_table where name <> "Jeremy" and (some other condition)
...

在该查询期间构造了my_temp_table的位置(当查询完成运行时，my_temp_table不再存在)，并且看上去像

where my_temp_table is constructed during that query (it ceases to exist when the query finishes running) and will look like

Table my_temp_table
Name  num
One   1
Two   2
Three 3
Four  4

希望我可以在不执行选择一个名称，从所有双并集中选择1 num ..."的情况下执行此操作

And hopefully I can do this without the ol' "select one name,1 num from dual union all ..."

这可行吗?

推荐答案

WITH子句听起来像是您所描述的最接近的事物.但这要求您以某种方式生成数据.从DUAL选择可能是最简单的选项

The WITH clause is sounds like the closest thing to what you're describing. But that requires that you generate the data somehow. Selecting from DUAL is likely the easiest option

WITH my_temp_table AS (
  SELECT 'One' name, 1 num from dual union all
  SELECT 'Two', 2 from dual union all
  SELECT 'Three', 3 from dual union all
  SELECT 'Four', 4 from dual
)
SELECT *
  FROM my_temp_table 
       JOIN person ON (<<some join condition>>)
 WHERE <<some predicate>>

由于您不想合并一堆查询，因此可以执行类似的操作

Since you don't want to union a bunch of queries, you could do something like

WITH my_temp_table AS (
  select level num,
         initcap( to_char( to_date( level, 'J' ),
                           'JSP' )) name
    from dual
 connect by level <= 4
)
...

这篇关于在Oracle SQL中构造一个临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！