having和where的区别

差异之处：
Having：用于分组后过滤数据，可以使用聚合函数
Where：用于分组前过滤数据，不可使用聚合函数
相同之处：
都是用于过滤数据的。

案例分析

语句执行顺序：from->where->group by ->having->order->select
从上面的执行顺序可以知道where优先级比having高。

例子1

SQL> select count(*),fid from t1 group by fid having fid=1;

LINEID     COUNT(*)             FID        
---------- -------------------- -----------
1          166                  1


1   #NSET2: [510, 250->1, 4] 
2     #PRJT2: [510, 250->1, 4]; exp_num(2), is_atom(FALSE) 
3       #SLCT2: [510, 250->1, 4]; T1.FID = 1
4         #SAGR2: [509, 10000->10000, 4]; grp_num(1), sfun_num(1), opt_num(0), distinct_flag[0]; slave_empty(0) keys(T1.FID)
5           #PARALLEL: [317, 3000000->3000000, 4]; scan_type(FULL)
6             #SSCN: [317, 3000000->3000000, 4]; IDX_DM_T1_FID(T1); btr_scan(1)

Statistics
-----------------------------------------------------------------
        0	    data pages changed
        0	    undo pages changed
        2440	    logical reads
        0	    physical reads
        0	    redo size
        197	    bytes sent to client
        119	    bytes received from client
        1	    roundtrips to/from client
        0	    sorts (memory)
        0	    sorts (disk)
        1	    rows processed
        0	    io wait time(ms)
        38	    exec time(ms)


used time: 38.918(ms). Execute id is 703.

例子2

SQL> select count(*),fid from t1 where fid=1 group by fid;

LINEID     COUNT(*)             FID        
---------- -------------------- -----------
1          166                  1


1   #NSET2: [1, 1->1, 4] 
2     #PRJT2: [1, 1->1, 4]; exp_num(2), is_atom(FALSE) 
3       #SLCT2: [1, 1->1, 4]; exp_sfun9 > 0
4         #AAGR2: [1, 1->1, 4]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
5           #PARALLEL: [1, 166->166, 4]; scan_type(FULL)
6             #SSEK2: [1, 166->166, 4]; scan_type(ASC), IDX_DM_T1_FID(T1), scan_range[1,1]

Statistics
-----------------------------------------------------------------
        0	    data pages changed
        0	    undo pages changed
        136	    logical reads
        0	    physical reads
        0	    redo size
        190	    bytes sent to client
        118	    bytes received from client
        1	    roundtrips to/from client
        0	    sorts (memory)
        0	    sorts (disk)
        1	    rows processed
        0	    io wait time(ms)
        1	    exec time(ms)


used time: 1.606(ms). Execute id is 704.

上面例子的结果都是一样的，但是执行效率是where更快。
过滤数据上，前面也提到where优先级高于having，也就是说where先过滤数据再分组，having是先分组再过滤数据。

总结

在实际项目中，有时遇到上面的情况。像这类问题的优化处理原则：先过滤后分组。不用聚合函数过滤数据则用where。