oracle SQL：带参数的WITH子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26278938/
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
SQL: WITH clause with parameters?
提问by Catherine Gasnier
In Oracle SQL Developer, I am using a WITH clause, in this (simplified) way:
在 Oracle SQL Developer 中，我以这种（简化的）方式使用 WITH 子句：
WITH
foos AS
SELECT *
FROM my_table
WHERE field = 'foo'
bars AS
SELECT *
FROM my_table
WHERE field = 'bar'
SELECT *
FROM foo
INNER JOIN bar
ON foo.id = bar.id
I would like to be able to factor out the 'foo' and 'bar' strings, so that I can have something like:
我希望能够分解出 'foo' 和 'bar' 字符串，以便我可以有类似的东西：
WITH
subq(my_arg) AS
SELECT *
FROM my_table
WHERE field = my_arg
SELECT *
FROM subq('foo')
INNER JOIN subq('bar')
ON subq('foo').id = subq('foo').id
Because, foosand barsare actually much bigger than this, and there are nut just two of them, so it is getting a bit hard to maintain.
因为，foos和bars实际上比这个大多了，而且有螺母只是他们两个，所以它是有点难以维持。
I know this may be not possible with a WITH clause, but what would be the best solution to avoid writing this subquery multiple times? This may be actually quite simple, but I am quite new to SQL...
我知道使用 WITH 子句可能无法做到这一点，但是避免多次编写此子查询的最佳解决方案是什么？这实际上可能很简单，但我对 SQL 很陌生......
Thanks for your help.
谢谢你的帮助。
回答by Bohemian
It seems this may be what you want:
看来这可能是你想要的：
SELECT *
FROM my_table foo
JOIN my_table bar ON foo.id = bar.id
JOIN my_table baz ON foo.id = baz.id
WHERE foo.field = 'foo'
AND bar.field = 'bar'
AND baz.field = 'baz'
If the WITH clause is doing a lot (and worth not repeating):
如果 WITH 子句做了很多事情（并且不值得重复）：
WITH cte AS SELECT * FROM mytable <with some complex SQL>
SELECT *
FROM cte foo
JOIN cte bar ON foo.id = bar.id
JOIN cte baz ON foo.id = baz.id
WHERE foo.field = 'foo'
AND bar.field = 'bar'
AND baz.field = 'baz'
回答by neshkeev
Try this:
尝试这个：
WITH subq AS (
SELECT *
FROM my_table
)
SELECT *
FROM subq s1
, subq s2
WHERE s1.id = s2.id
AND s1.field = 'foo'
AND s2.field = 'bar'
Or you can use pipelinedfunction like this:
或者你可以像这样使用流水线函数：
CREATE TYPE t_tf_tab IS TABLE OF MY_TABLE%ROWTYPE;
CREATE OR REPLACE FUNCTION get_table_vals (
p_val IN VARCHAR
)
RETURN t_tf_tab
PIPELINED
AS
BEGIN
FOR i IN (SELECT * FROM MY_TABLE WHERE FIELD = p_val)
PIPE ROW(t_tf_row(i.id, i.field, ...));
END LOOP;
RETURN;
END;
SELECT *
FROM TABLE(get_table_vals('foo')) s1
, TABLE(get_table_vals('bar')) s2
where s1.id = s2.id
回答by borjab
You can reuse a WITH expression in the next one. But as far as I know you cannot parametrize it. So may be this could help:
您可以在下一个中重用 WITH 表达式。但据我所知，您无法对其进行参数化。所以这可能会有所帮助：
WITH
foosAndbars AS
(SELECT *
FROM [Very complex query] ).
foos AS (
SELECT *
FROM foosAndbars
WHERE field = 'foo'),
bars AS (
SELECT *
FROM foosAndbars
WHERE field = 'bar')
SELECT *
FROM foo
INNER JOIN bar
ON foo.id = bar.id
