oracle sql选择带有GROUP BY和HAVING子句的语法 [英] oracle sql select syntax with GROUP BY and HAVING clause

问题描述

我一直在通过一些sql语法来学习oracle sql考试，我发现了一些令人困惑的

，它基于官方引用，选择语法如下：

  SELECT 
 [提示] 
 [{{DISTINCT | UNIQUE} | ALL}] 
 select_list 
 FROM {table_reference | join_clause | （join_clause）} 
 [，{table_reference | join_clause | （join_clause）}] ... 
 [where_clause] 
 [hierarchical_query_clause] 
 [group_by_clause] 
 [HAVING条件] 
 [model_clause] 
  

基于此，GROUP BY子句之前不能包含HAVING子句。但是，如果我要在测试服务器中执行以下sql：

  select 
 department_id，count（*）
 from 
雇员
 
 count（*）> 6 
 group by 
 department_id; 
  

它不会产生语法错误，有人可以解释这个吗？我不喜欢认为参考文档是错误的，但如果是这样的话，我需要一些确认。

这里：

使用HAVING子句将返回行的组限制为指定条件为TRUE的那些组。如果您省略此子句，那么数据库将返回所有组的摘要行。

在where_clause和hierarchical_query_clause之后指定GROUP BY和HAVING。如果您同时指定GROUP BY和HAVING，则它们可以以任意顺序出现。




I been going thru some of the sql syntax to study for the oracle sql exam, I found something rather confusing

based on the official references, the select syntax is as follow :

SELECT
    [ hint ]
    [ { { DISTINCT | UNIQUE } | ALL } ]
   select_list
     FROM { table_reference | join_clause | ( join_clause ) }
            [ , { table_reference | join_clause | (join_clause) } ] ...
     [ where_clause ]
     [ hierarchical_query_clause ]
     [ group_by_clause ]
     [ HAVING condition ]
     [ model_clause ]

based on this you cannot have the HAVING clause before the GROUP BY clause . However if i were to execute the following sql in the test server :

select 
   department_id , count (*)      
from 
    employees 
having 
    count(*) > 6 
group by 
    department_id ; 

it does not produce a syntax error , can some one help explain this ? I don't like to think that the reference docs is wrong , but if so I need some confirmation.

解决方案

As stated here:

Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE. If you omit this clause, then the database returns summary rows for all groups.

Specify GROUP BY and HAVING after the where_clause and hierarchical_query_clause. If you specify both GROUP BY and HAVING, then they can appear in either order.

这篇关于oracle sql选择带有GROUP BY和HAVING子句的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！