oracle 为什么一个视图的联合可以工作，但是当我创建一个视图时，它失败了

alen0pnh 于 2023-06-22 发布在 Oracle

关注(0)|答案(1)|浏览(88)

在ORACLE中，我试图创建一个视图，它是一个视图的并集：

CREATE VIEW "TEST_ALL"
AS 

( SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW1
UNION ALL

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW
UNION ALL

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW3
UNION ALL

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW4
UNION ALL

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW5
UNION ALL

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,  M_ID, COUNT(DISTINCT OBJECT_ID) as RESULT
FROM VIEW6
)

这不管用吗但是，当我删除M_ID时，它创建了视图。
当我只是联合所有的内部意见，它的工作。有人能帮帮我吗？

oracle

来源：https://stackoverflow.com/questions/76435021/why-is-that-a-union-of-views-works-but-when-i-create-a-view-out-of-union-of-view

1条答案

按热度按时间

js81xvg61#

在查询中：

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,
       M_ID,
       COUNT(DISTINCT OBJECT_ID) as RESULT
FROM   VIEW1

然后：

SYS_EXTRACT_UTC(SYSTIMESTAMP)对于所有行有效地是恒定的;
COUNT(DISTINCT OBJECT_ID)是聚合;和/或
M_ID是一个非聚合列。

您混合了聚合列和非聚合列，并且没有GROUP BY子句。
将每个子查询更改为：
1.

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,
       M_ID,
       COUNT(DISTINCT OBJECT_ID) as RESULT
FROM   VIEW1
GROUP BY M_ID

1.或者使用解析函数来生成计数：

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) as DT,
       M_ID,
       COUNT(DISTINCT OBJECT_ID) OVER () as RESULT
FROM   VIEW1

并对每个视图重复此操作。

赞(0）回复(0）举报 2023-06-22

我来回答

oracle 为什么一个视图的联合可以工作，但是当我创建一个视图时，它失败了

1条答案

相关问题

热门标签

最新问答