与Oracle上的INSERT INTO结合使用时，有效的GROUP BY查询不起作用 [英] Valid GROUP BY query doesn't work when combined with INSERT INTO on Oracle

问题描述

我正在尝试写一个ININSERT INTO来完成一些DISTINCT/GROUP BY工作.该查询可以作为select语句很好地运行，但是如果将它包装到INSERT INTO中，将无法正常工作.

I'm trying to write an INSERT INTO that does a some DISTINCT/GROUP BY work. The query runs perfectly fine as a select statement, but will not work if it's wrapped into an INSERT INTO.

INSERT INTO MasterRecords
  (BatchRecordRecordID, SourceID, BatchID)
SELECT RecordID, SourceID, BatchID
FROM (
    SELECT RecordID, BatchID, 101 AS SourceID
    FROM BatchRecords
    WHERE BatchID = 150
    GROUP BY RecordID, BatchID
) BR

这使我赚钱:

SQL错误:ORA-00979:不是GROUP BY表达式

SQL Error: ORA-00979: not a GROUP BY expression

但是，如果我只删除INSERT INTO代码，它运行得很好:

But if I remove just the INSERT INTO code, it runs perfectly fine:

SELECT RecordID, SourceID, BatchID
FROM (
    SELECT RecordID, BatchID, 101 AS SourceID
    FROM BatchRecords
    WHERE BatchID = 150
    GROUP BY RecordID, BatchID
) BR

结果:

3   101 150
5   101 150
6   101 150
2   101 150
4   101 150
8   101 150
7   101 150
1   101 150

我的假设是，在INSERT INTO select语句中不允许使用GROUP BY，但是我几乎找不到任何文档来证实这一点.

My assumption is that GROUP BY's are not allowed inside INSERT INTO select statements but I can find almost no documentation confirming this.

推荐答案

我到达这里是为了解决类似的情况，因此在我看来这种问题仍然存在.

I arrived here trying to solve a similar situation so it seems to me that this kind of problem still appears.

就我而言，避免了任何优化程序转换，就成功了.

In my case, avoiding any optimizer transformation, did the trick.

我对"intoed" SELECT语句应用了NO_QUERY_TRANSFORMATION提示，该错误消失了.

I applied a NO_QUERY_TRANSFORMATION hint to the "intoed" SELECT statement and the error disappeared.

在这个问题上，我应该将其重写为:

In the case of this question, I should rewrite it as:

INSERT INTO MasterRecords
  (BatchRecordRecordID, SourceID, BatchID)
SELECT /*+NO_QUERY_TRANSFORMATION*/ RecordID, SourceID, BatchID
FROM (
    SELECT RecordID, BatchID, 101 AS SourceID
    FROM BatchRecords
    WHERE BatchID = 150
    GROUP BY RecordID, BatchID
) BR

这篇关于与Oracle上的INSERT INTO结合使用时，有效的GROUP BY查询不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！