非常简单的SQL不是GROUP BY表达式 - Oracle [英] really simple SQL Not a GROUP BY expression - Oracle

问题描述

我尝试通过一些其他帖子的表达错误来了解关于不是群组的问题，但是他们都提到了诸如MAX，MIN等的群组函数。我没有使用它，它是一个非常简单的查询，返回这个

  SELECT * 
 FROM ad_voarnet_atendimento_pista 
 WHERE is_closed = 0 
 GROUP BY前缀
 ORDER BY前缀
  

我在这里做错了什么？

编辑：

预期的结果与MySQL给我的查询相同。它会排除PREFIXO列的每个重复值。我只需要提到的列中每个值的1条记录。
错误信息是这样的：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ORA-00979：不是一个GROUP BY表达式

解决方案

GROUP BY 在集合函数（例如 MIN（）MAX（）SUM（）COUNT（））的上下文之外无用，除了可能对重复数据行进行重复数据删除操作。只要删除它。如果您希望重复删除结果，请改用 DISTINCT 。如果您使用 DISTINCT ，那么除非您更具体地介绍 SELECT 中的列，不包括主键列。

  SELECT 
 DISTINCT * 
 FROM 
 AD_VOARNET_ATENDIMENTO_PISTA 
 WHERE IS_CLOSED = 0 
 ORDER BY PREFIXO 
  

GROUP BY 有时会与 ORDER BY 混淆。您已经有 ORDER BY PREFIX0 ，

I tried to read about not a group by expression errors from some other posts, but all of them mention group functions such as MAX, MIN, etc. I'm not using any of it and it's a really simple query returning this sort of error.

SELECT * 
FROM   ad_voarnet_atendimento_pista 
WHERE  is_closed = 0 
GROUP  BY prefixo 
ORDER  BY prefixo 

What am I doing wrong here?

Edit:

The expected result is the same that MySQL would give me with this query. It would exclude every duplicated value of the column PREFIXO. I want only 1 record of each value in the mentioned column. The error message is this:

[Err] ORA-00979: not a GROUP BY expression

解决方案

The GROUP BY is not useful outside of the context of an aggregate function like MIN() MAX() SUM() COUNT(), except perhaps to deduplicate rows. Just remove it. If you are looking to deduplicate results, use DISTINCT instead. If you use DISTINCT, it won't be of much value unless you are more specific about the columns in the SELECT list, excluding the primary key column.

SELECT
  DISTINCT * 
FROM 
  AD_VOARNET_ATENDIMENTO_PISTA 
WHERE IS_CLOSED = 0
ORDER BY PREFIXO

GROUP BY is sometimes confused with ORDER BY. You already have an ORDER BY PREFIX0,

这篇关于非常简单的SQL不是GROUP BY表达式 - Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！