将一组定义好的值构建成CTE列表

Question

有没有办法为硬编码的值列表构建CTE？例如，我有一个已知ID列表（即101、102、105、200...），如何能够创建一个名为ID的列的CTE，但所有ID值都是在查询中硬编码的？顺便说一下，我需要在Oracle中运行此查询。谢谢！

- mickey_tx

Answer 1

编辑：之前提供的解决方案仅适用于MSSQL。因此，我添加了一个Oracle解决方案。我会保留原始答案。

我想到了另一种解决方案（虽然Justin Cave提供的那个似乎更好）-使用临时表。

以下是它的样子：

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);

这应该是适用于Oracle数据库的有效解决方案。

以下是原始答案

---

我遇到过类似的问题，这是我的解决方案（如评论中所述，这仅适用于MSSQL，而不适用于Oracle DB）

WITH cte AS (
    SELECT * FROM (
        VALUES
            (1, 2, 3, ...),
            (2, 3, 4, ...)
        ) AS a (col1, col2, col3, ...)
    )
INSERT INTO ...

希望这有帮助：)

- Zax

---

此语法在Oracle中无效。 - Jon Heller

因此，对于造成的混淆我感到抱歉，并感谢您提供的信息。希望这能帮助其他在 MSSQL 中寻找相同信息的人。 - Zax

好的，我编辑了之前的答案，并添加了我认为适用于Oracle的有效解决方案。 - Zax

注意：VALUES 仅适用于 SQL Server 2008（v10）及更高版本。 - AjV Jsy

希望这能帮助到其他在 MSSQL 中寻找相同内容的人，没错，就是我！ - MarredCheese

Answer 2

你可以像这样做

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
  ...
)

不过，根据你实际想要完成的目标，你可能需要声明一个集合并使用它（带或不带解析逗号分隔字符串的函数）。

CREATE TYPE num_tbl
    AS TABLE OF NUMBER;

WITH cte AS (
  SELECT column_value
    FROM TABLE( num_tbl( 101, 102, 105, 200 ))
)

- Justin Cave

---

仅补充一点，最近的版本允许为子查询因子子句定义列别名，并且可以单独定义，可能使查询子句更加整洁：WITH cte (id) as (SELECT 101 from dual ... http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702 - David Aldridge

Answer 3

您可以使用集合类型来实现这一点，可以将它们作为绑定参数提交或在查询中创建它们。

正如Justin Cave所建议的那样，您可以创建自己的集合类型，但是SYS包含一些默认定义的基本类型，例如SYS.ODCIDATELIST（用于DATE），SYS.ODCINUMBERLIST（用于NUMBER/NUMERIC），SYS.ODCIVARCHAR2LIST（用于VARCHAR2，最多4000个字符），其中一些似乎并不是专门为内部使用而设计的。

由于您正在使用整数ID，因此内置的数字集合应该完全可以胜任：

-- column_value and table() work similarily to UNNEST() in Postgres
SELECT column_value as selected_id FROM TABLE(
    SYS.ODCINUMBERLIST(101, 102, 105, 200)
)

根据您的主机语言支持情况，您也可以将列表作为参数发送。例如，在Python中直接使用cx_Oracle：

import cx_Oracle

query = 'select column_value as selected_id FROM TABLE(:id_list)'

conn = cx_Oracle.connect('user', 'hunter2', '//192.0.2.5:1521/mydb')

OdciNumberList = conn.gettype("SYS.ODCINUMBERLIST")
id_list = OdciNumberList.newobject()

id_list.extend([101, 102, 105, 200])

cur = conn.cursor()
res = cur.execute(query, id_list=id_list )
res.fetchall() # [(101,), (102,), (105,), (200,)]

那么您只需要将其包装在公用表表达式（CTE）中即可。

- VLRoyrenn

---

此解决方案对于非常大的WHERE ... IN列表也很方便，如最近 cx_Oracle 文档中所示。值得注意的是，gettype()相对较“昂贵”，需要几次往返，因此在同一连接中需要多次执行此操作时，请保留OdciNumberList，不要重复调用 'gettype()'. - Christopher Jones