oracle,mysql,pg的having转换为where规则

having普通列（非聚合条件）提升到where里，在分组之前过滤，如果having的普通列可以走索引效率好，这种提升有好处
测试下来，只有pg支持，MySQL文档说支持，但是测试部支持，oracle不支持
能放到where里的条件不要放到having里，是编写SQL的准则之一


---MySQL
文档8.2.1.1 WHERE Clause Optimization
HAVING is merged with WHERE if you do not use GROUP BY or aggregate functions (COUNT(), MIN(), and so on).


这里说having在没有group by或聚合函数情况下，可以转到where里，测试也不行


##mysql支持查找非分组列，支持无分组的having 列条件，pg和oracle不支持这种，id有索引但是没有走

explain analyze select * from sub_t having id<10\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (sub_t.id < 10) (cost=10059.85 rows=99876) (actual time=0.837..64.590 rows=900 loops=1)
    -> Table scan on sub_t (cost=10059.85 rows=99876) (actual time=0.032..59.453 rows=99990 loops=1)

1 row in set (0.06 sec)

## 改成where走索引

explain analyze select * from sub_t where id<10\G
*************************** 1. row ***************************
EXPLAIN: -> Index range scan on sub_t using idx1_sub_t over (id < 10), with index condition: (sub_t.id < 10) (cost=405.26 rows=900) (actual time=0.032..2.085 rows=900 loops=1)

explain analyze select id,max(name) from sub_t group by id having id<10\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (sub_t.id < 10) (cost=20047.45 rows=99876) (actual time=0.323..215.649 rows=9 loops=1)
    -> Group aggregate: max(sub_t.`name`) (cost=20047.45 rows=99876) (actual time=0.322..215.389 rows=1000 loops=1)
        -> Index scan on sub_t using idx1_sub_t (cost=10059.85 rows=99876) (actual time=0.038..181.364 rows=99990 loops=1)

1 row in set (0.21 sec)

----pg
不支持这种语法

explain analyze select * from sub_t having id<10;
ERROR: syntax error at or near "explain"
LINE 2: explain analyze select * from sub_t having id<10;
        ^

pg支持having是普通列条件，直接提升到where里在分组计算之前过滤，这样可以走索引

explain analyze select id,max(name) from sub_t group by id having id<10;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 GroupAggregate (cost=0.29..8.62 rows=10 width=36) (actual time=0.017..0.023 rows=9 loops=1)
   Group Key: id
   -> Index Scan using idx1_sub_t on sub_t (cost=0.29..8.47 rows=10 width=7) (actual time=0.008..0.011 rows=9 loops=1)
         Index Cond: (id < 10)
 Planning Time: 0.158 ms
 Execution Time: 0.052 ms

oracle和mysql一样，也不支持having条件提升到where里 

select id,max(name) from sub_t group by id having id<10;

no rows selected

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1839928944

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 76 (11)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 2 | 24 | 76 (11)| 00:00:01 |
| 3 | TABLE ACCESS FULL| SUB_T | 99991 | 1171K| 69 (2)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<10)


Statistics
----------------------------------------------------------
         79  recursive calls
          0  db block gets
        316  consistent gets
        239  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        406  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0 rows processed

select id,max(name) from sub_t where id<10 group by id;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1344960120

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 1 | 12 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SUB_T | 1 | 12 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_SUB_T | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<10)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          5  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        410  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0 rows processed