PostgreSQL HAVING 子句

596. 超过5名学生的课

courses表结构

create table courses( student varchar(20), class varchar(20) );

insert into courses(student, class) values ('A', 'Math'), ('B', 'English'), ('C', 'Math'), ('D', 'Biology'), ('E', 'Math'), ('F', 'Computer'), ('G', 'Math'), ('H', 'Math'), ('I', 'Math'),('A', 'Math'), ('A', 'Math'), ('B', 'English');

select * from courses;
 student |  class
---------+----------
 A       | Math
 B       | English
 C       | Math
 D       | Biology
 E       | Math
 F       | Computer
 G       | Math
 H       | Math
 I       | Math
 A       | Math
 A       | Math
 B       | English
(12 rows)

子查询写法

courses表结构

select a.class from (select class, count(distinct student) as ct from courses group by class) as a where a.ct > 4;
 class
-------
 Math
(1 row)

explain analyze select a.class from (select class, count(distinct student) as ct from courses group by class) as a where a.ct > 4;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on a  (cost=41.16..48.53 rows=67 width=58) (actual time=0.079..0.081 rows=1 loops=1)
   ->  GroupAggregate  (cost=41.16..47.86 rows=67 width=66) (actual time=0.077..0.079 rows=1 loops=1)
         Group Key: courses.class
         Filter: (count(DISTINCT courses.student) > 4)
         Rows Removed by Filter: 3
         ->  Sort  (cost=41.16..42.56 rows=560 width=116) (actual time=0.041..0.044 rows=12 loops=1)
               Sort Key: courses.class
               Sort Method: quicksort  Memory: 25kB
               ->  Seq Scan on courses  (cost=0.00..15.60 rows=560 width=116) (actual time=0.018..0.023 rows=12 loops=1)
 Planning Time: 0.394 ms
 Execution Time: 0.129 ms
(11 rows)

HAVING 子句介绍及语法

HAVING 子句可以让我们筛选分组后的各组数据。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

HAVING写法

SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >= 5;
 class
-------
 Math
(1 row)

explain analyze SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >= 5;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=41.16..47.86 rows=67 width=58) (actual time=0.078..0.079 rows=1 loops=1)
   Group Key: class
   Filter: (count(DISTINCT student) >= 5)
   Rows Removed by Filter: 3
   ->  Sort  (cost=41.16..42.56 rows=560 width=116) (actual time=0.036..0.038 rows=12 loops=1)
         Sort Key: class
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on courses  (cost=0.00..15.60 rows=560 width=116) (actual time=0.015..0.019 rows=12 loops=1)
 Planning Time: 0.127 ms
 Execution Time: 0.195 ms
(10 rows)

比较

两种写法从查询计划来看实现的功能一致，having写法更加简洁。