Oracle 11g: The difference between WHERE and HAVING

SQL> select * from sys.SHIP_CABINS ;

SHIP_CABIN_ID    SHIP_ID ROOM_ ROOM_STYLE ROOM_TYPE            WINDOW         GUESTS      SQ_FT BALCONY_SQ_FT
------------- ---------- ----- ---------- -------------------- ---------- ---------- ---------- -------------
            1            102   Suite      Standard             Ocean               4        533
            2            103   Stateroom  Standard             Ocean               2        160
            3            104   Suite      Standard             None                4        533
            4            105   Stateroom  Standard             Ocean               3        205
            5            106   Suite      Standard             None                6        586
            6            107   Suite      Royal                Ocean               5       1524
            7            108   Stateroom  Large                None                2        211
            8            109   Stateroom  Standard             None                2        180
            9            110   Stateroom  Large                None                2        225
           10            702   Suite      Presidential         None                5       1142
           11            703   Suite      Royal                Ocean               5       1745
           12            704   Suite      Skyloft              Ocean               8        722

 Group by without WHERE and HAVING

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type ;

ROOM_STYLE ROOM_TYPE             GUEST SUM
---------- -------------------- ----------
Stateroom  Standard                      7
Suite      Standard                     14
Stateroom  Large                         4
Suite      Skyloft                       8
Suite      Royal                        10
Suite      Presidential                  5

HAVING

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type having room_type<>'Royal' ;ROOM_STYLE ROOM_TYPE             GUEST SUM
---------- -------------------- ----------
Stateroom  Standard                      7
Suite      Standard                     14
Stateroom  Large                         4
Suite      Skyloft                       8
Suite      Presidential                  5

WHERE

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins where room_type<>'Royal' group by room_style, room_type ;

ROOM_STYLE ROOM_TYPE             GUEST SUM
---------- -------------------- ----------
Stateroom  Standard                      7
Suite      Standard                     14
Stateroom  Large                         4
Suite      Skyloft                       8
Suite      Presidential                  5

Summary:

There is no difference between them when there is no aggregate function in HAVING clause.

However, we do this in HAVING.

SQL> select room_style, room_type, sum(GUESTS) "GUEST SUM" from sys.ship_cabins group by room_style, room_type having sum(GUESTS)>8 ;

ROOM_STYLE ROOM_TYPE             GUEST SUM
---------- -------------------- ----------
Suite      Standard                     14
Suite      Royal                        10

 

 There is no way to filter out sum(GUESTS)>8 entries using WHERE, because it doesn't allow to use Aggregate function in WHERE clause.

转载于:https://www.cnblogs.com/duadu/archive/2012/02/13/6335531.html