where子句中不能使用聚集函数

编译以下语句出错：

select  s.segment_name,s.segment_type,s.tablespace_name,s.next_extent

  from dba_segments s

 where not exists (select 1

                                   from dba_free_space f

                                  where s.tablespace_name = f.tablespace_name

                                             and max(f.bytes) > s.next_extent);

 

报错型号打在max(f.bytes)下：

ORA - 00934：group function is not allowed here

 

原来，在where中不能出现聚集函数，使用聚集函数要带having

正确SQL写法如下：

select  s.segment_name,s.segment_type,s.tablespace_name,s.next_extent

  from dba_segments s

 where not exists (select 1

                                   from dba_free_space f

                                  where s.tablespace_name = f.tablespace_name

                                             having max(f.bytes) > s.next_extent);