Oracle WITH CLAUSE 不起作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/801586/
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
Oracle WITH CLAUSE not working?
提问by Tony Andrews
I'm trying to use a WITH clause in a query but keep getting the message
我正在尝试在查询中使用 WITH 子句，但不断收到消息
ORA-00942: table or view does not exist
ORA-00942: 表或视图不存在
I've tried to create a simple query just as an example here:
我试图在这里创建一个简单的查询作为示例：
WITH
test AS
(
SELECT COUNT(Customer_ID) FROM Customer
)
SELECT * FROM test;
WITH
test AS
(
SELECT COUNT(Customer_ID) FROM Customer
)
SELECT * FROM test;
But even this dosen't work, it just gives the message:
但即使这样也行不通，它只会给出以下信息：
SELECT * FROM test; 2 3 4 5 6 SQL>
SELECT * FROM test
* ERROR at line 1:
ORA-00942: table or view does not exist
选择 * 从测试；2 3 4 5 6 SQL>
SELECT * FROM test
* 第 1 行出错：
ORA-00942：表或视图不存在
I've never used the WITH clause before, is there something simple I'm missing here? I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod Any advise would be appreciated. Thanks.
我以前从未使用过 WITH 子句，这里有没有我遗漏的简单内容？我使用的是 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 任何建议将不胜感激。谢谢。
回答by Tony Andrews
I believe you have a blank line in your script between the WITH clause and the SELECT:
我相信您的脚本中 WITH 子句和 SELECT 之间有一个空行：
SQL> WITH
2 test AS
3 (
4 SELECT COUNT(Customer_ID) FROM Customer
5 )
6
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
That is consistent with the fact that you got the error reported as being on "line 1" and SQL "select * from test", when this SQL should be on "line 6".
这与您将错误报告为在“第 1 行”和 SQL“select * from test”上的事实一致，而此 SQL 应在“第 6 行”上。
回答by dpbradley
Your example works - just tried it (SQL*Plus log follows):
您的示例有效 - 刚刚尝试过（SQL*Plus 日志如下）：
SQL> create table customer
2 (customer_id number);
Table created.
SQL> with
2 test as
3 (select count(customer_id)
4 from customer
5 )
6 select * from test;
COUNT(CUSTOMER_ID)
------------------
0
Are you sure that you have privileges on the customer table or don't need a schema qualifier for it (if it is in a different schema)?
您确定您对 customer 表有权限还是不需要模式限定符（如果它在不同的模式中）？
回答by Oliver Michels
Take a look at this example
看看这个例子
EDIT
编辑
a very basic sample:
一个非常基本的示例：
create table emp (emp_id number, dept_id number);
insert into emp values (1,20);
insert into emp values (2,20);
insert into emp values (3,20);
insert into emp values (4,30);
with
emp_counter as (select count(distinct emp_id) from emp),
dept_counter as (select count(distinct dept_id) from emp)
select * from emp_counter, dept_counter;
COUNT(DISTINCTEMP_ID) COUNT(DISTINCTDEPT_ID)
--------------------- ----------------------
4 2
回答by Greg Ogle
The error which you are getting means literally that the table for view does not exist in your current schema and has no synonym visible to your schema. For example, if I login as greg, and the table is in bob, then I should reference the table as bob.test.
您收到的错误字面意思是您当前的架构中不存在视图表，并且您的架构没有可见的同义词。例如，如果我以 greg 身份登录，并且表在 bob 中，那么我应该将该表引用为 bob.test。
SELECT * FROM bob.test
As for the WITH syntax, I am not familiar, but other answers are covering that fine.
至于 WITH 语法，我不熟悉，但其他答案涵盖了这一点。
