oracle 将定义的值列表构建到 CTE 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22850382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Build a list of defined values into CTE
提问by mickey_tx
Is there any way to build a CTE for a list of hard coded values? For example, I have a list of known IDs (i.e. 101,102,105,200...), how would I be able to create a CTE with one column called ID but all the ID values are hard coded in the query? BTW, I need to run this query in Oracle. Thanks!
有没有办法为硬编码值列表构建 CTE？例如，我有一个已知 ID 的列表（即 101,102,105,200...），我如何能够创建一个名为 ID 的列的 CTE，但所有 ID 值都在查询中进行了硬编码？顺便说一句，我需要在 Oracle 中运行此查询。谢谢！
回答by Justin Cave
You could do something like
你可以做类似的事情
WITH cte AS (
SELECT 101 id FROM dual UNION ALL
SELECT 102 FROM dual UNION ALL
SELECT 105 FROM dual UNION ALL
SELECT 200 FROM dual UNION ALL
...
)
Depending on what you are really trying to accomplish, though, you may want to declare a collection and use that (with or without a function that parses a comma-separated string)
但是，根据您真正想要完成的任务，您可能想要声明一个集合并使用它（使用或不使用解析逗号分隔字符串的函数）
CREATE TYPE num_tbl
AS TABLE OF NUMBER;
WITH cte AS (
SELECT column_value
FROM TABLE( num_tbl( 101, 102, 105, 200 ))
)
回答by Zax
EDIT: previously advised solution works only for MSSQL. Therefore I am adding an Oracle solution. I am keeping the original answer below.
编辑：先前建议的解决方案仅适用于 MSSQL。因此，我添加了一个 Oracle 解决方案。我保留下面的原始答案。
I thought of one more solution (though the one provided by Justin Cave still seems a bit better) - using temporary tables.
我想到了另一种解决方案（尽管 Justin Cave 提供的解决方案看起来仍然更好一些）——使用临时表。
Here is how it may look like
这是它的样子
CREATE GLOBAL TEMPORARY TABLE temp_ids
(id INT)
ON COMMIT PRESERVE ROWS;
INSERT INTO ids (id) VALUES (101);
INSERT INTO ids (id) VALUES (102);
INSERT INTO ids (id) VALUES (103);
This should be a valid solution for Oracle database.
这应该是 Oracle 数据库的有效解决方案。
Original answer below
原答案如下
I have come across similar issue and here is my solution (this does not work on Oracle DB as mentioned in comments, only MSSQL though)
我遇到了类似的问题，这是我的解决方案（这在评论中提到的 Oracle DB 上不起作用，但仅适用于 MSSQL）
WITH cte AS (
SELECT * FROM (
VALUES
(1, 2, 3, ...),
(2, 3, 4, ...)
) AS a (col1, col2, col3, ...)
)
INSERT INTO ...
Hope this helps :)
希望这可以帮助 ：）
