Oracle-创建临时结果集以在查询中使用 [英] Oracle- create a temporary resultset for use in a query

问题描述

如何在不创建表和插入数据的情况下创建用于SQL的临时结果集?

How do I create a temporary result set for use in an SQL without creating a table and inserting the data?

示例:例如，我列出了10个代码.我想将其放入查询中，然后查询数据库以查看该临时列表中的哪些代码在表中不存在.

Example: I have a list of, say 10 codes for example. I want to put this into a query, and then query the database to see which codes in this temporary list do not exist in a table.

如果它已经在表中，我可以做类似的事情:

If it was already in a table, I could do something like:

SELECT
  ITEM_CODE
FROM
  TEMP_ITEMS
MINUS
SELECT
   ITEM_CODE
FROM
   M_ITEMS

他们是不使用PL/SQL以及纯SQL在查询之前创建临时行集的方法吗? 请不要回答类似的内容:

Is their a way without using PL/SQL, and pure SQL to create a temporary rowset before querying? Please don't answer with something like:

SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL

我有点想在哪里可以在IN语句中提供代码，并将其转换为行以供以后的查询使用.

I am sort of thinking of something where I can provide my codes in an IN statement, and it turns that into rows for use in a later query.

因此每个人都知道我的目标，基本上，有时候我会得到一个产品代码列表，我需要查找该列表中的哪些代码未在我们的系统中设置.我想要一种快速的方法将其放入SQL语句中，以便查看哪些不在系统中(而不是导入数据等).我通常将这些放到excel中，然后做一个公式，例如:

so everyone knows my objective here, basically I sometimes get a list of product codes that I need to find which ones in the list are not setup in our system. I want a quick way to throw this into an SQL statement so I can see which ones are not in the system (rather than importing data etc). I usually put these into excel, then do a formula such as :

="'"&A1&"',"

这样我就可以创建用逗号分隔的列表.

So that I can create my comma separated list.

推荐答案

如果您使用的是oracle 11g，则可以这样做

If you are using oracle 11g you can do this

with t as 
(
 select (column_value).getnumberval() Codes from xmltable('1,2,3,4,5')
)
SELECT * FROM t
WHERE NOT EXISTS (SELECT 1 FROM M_ITEMS M WHERE codes = M.ITEM_CODE);

或

with t as 
(
 select (column_value).getstringval() Codes from xmltable('"A","B","C"')
)
SELECT * FROM t
WHERE NOT EXISTS (SELECT 1 FROM M_ITEMS M WHERE codes = M.ITEM_CODE);

这篇关于Oracle-创建临时结果集以在查询中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！