我有一个在 CLOB 上运行的查询，将列拆分并基于字符串进行过滤。这是查询：
WITH t AS (
SELECT
TRIM(typesettings) AS str
FROM
layout
) SELECT
regexp_substr(str,'(\d+\DINSTANCE\D\w*)',1,level)
FROM
t
WHERE
regexp_substr(str,'(\d+\DINSTANCE\D\w*)',1,level) LIKE '56_INSTANCE_%'
CONNECT BY
level <= regexp_count(str,'(\d+\DINSTANCE\D\w*)');
以下是上述查询的输出示例：
56_INSTANCE_324rtde32r
56_INSTANCE_4mkl4323o4
56_INSTANCE_rr312ek121
我想使用上述查询的结果从另一个表（称为日志）中选择 ID。我无法找出使用结果集进行另一个选择的正确方法。“with”符号让我失望。
任何人都知道如何做到这一点？
您可以通过这种方式在 WITH 子句中级联进一步的子查询：
WITH alias1 AS (
some-query
),
alias2 AS (
SELECT ... FROM alias1 ......
),
alias3 AS (
SELECT ... FROM alias2 ......
),
.....
.....
.....
alias99 AS (
SELECT ... FROM alias33 ....
)
/* the last query */
SELECT ...
FROM alias99 JOIN alias55 ON ....
WHERE id IN ( select id FROM alias47 )
....
您还可以使用整个 WITH 查询作为子查询：
SELECT * FROM (
WITH x as (
SELECT * FROM dual
)
SELECT * FROM x
);
或者：
SELECT * FROM dual
WHERE dummy IN (
WITH x as (
SELECT * FROM dual
)
SELECT * FROM x
);
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句