十四、HAVING和DISTINCT - 《PostgreSQL》

1.HAVING
- 1.1语法
- 1.2示例
2.DISTINCT
- 2.1语法
- 2.2示例

1.HAVING

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，

1.1语法

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

1.2示例

--company表中的信息如下，可以看到表中James、Paul是有重复的
runoobdb=# SELECT * FROM company;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
  6 | Kim   |  22 | South-Hall                                         |  45000
  7 | James |  24 | Houston                                            |  10000
  8 | Paul  |  24 | Houston                                            |  20000
  9 | James |  44 | Norway                                             |   5000
 10 | James |  45 | Texas                                              |   5000
(10 行记录)

--根据name进行分组，并且找出 name字段的计数大于2数据：
runoobdb=# SELECT name FROM company GROUP BY name HAVING COUNT(name) > 1;
 name
-------
 Paul
 James
(2 行记录)

--根据age,SUM(salary)进行分组，并且找出薪资大于20000的数据
runoobdb=# SELECT age,SUM(salary) FROM company GROUP BY age  HAVING SUM(salary) > 20000;
 age |  sum
-----+-------
  22 | 45000
  24 | 30000
  25 | 80000
  27 | 85000
(4 行记录)

--比较HAVING 和WHERE的区别
--尝试使用WHERE来实现
runoobdb=# SELECT age, SUM(salary) AS 薪资总和 FROM company  WHERE salary > 20000 GROUP BY age;
 age | 薪资总和
-----+----------
  22 |    45000
  25 |    65000
  27 |    85000
(3 行记录)

--只指定salary时没问题，但是24岁的人有两个，他们薪资之和是30000，这个数据是少了的
--想要包含两个24岁的人薪资总和的数据就要使用SUM(salary），下面尝试一下
runoobdb=# SELECT age, SUM(salary) AS 薪资总和 FROM company  WHERE SUM(salary) > 20000 GROUP BY age;
错误:  聚合函数不允许出现在WHERE中
第1行... age, SUM(salary) AS 薪资总和 FROM company  WHERE SUM(salary...
                                                          ^

--WHERE 过滤的是行，HAVING过滤的是分组

2.DISTINCT

DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录

2.1语法

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

2.2示例

--当前company表中的情况
runoobdb=# SELECT * FROM company;
 id | name  | age |                       address                        | salary
----+-------+-----+------------------------------------------------------+--------
  1 | Paul  |  32 | California                                           |  20000
  2 | Allen |  25 | Texas                                                |  15000
  3 | Teddy |  23 | Norway                                               |  20000
  4 | Mark  |  25 | Rich-Mond                                            |  65000
  5 | David |  27 | Texas                                                |  85000
  6 | Kim   |  22 | South-Hall                                           |  45000
  7 | James |  24 | Houston                                              |  10000
  8 | Paul  |  24 | Houston                                              |  20000
  9 | James |  44 | Norway                                               |   5000
 10 | James |  45 | Texas                                                |   5000
 11 | Paul  |  32 | California                                           |  20000
 12 | Allen |  25 | Texas                                                |  15000
 13 | haha  |  15 | 北京                                                 |  15000
(13 行记录)


--去除重复姓名后的数据
runoobdb=# SELECT DISTINCT name FROM company ;
 name
-------
 Teddy
 David
 Paul
 haha
 Kim
 Mark
 Allen
 James
(8 行记录)

--查看name,address字段，然后去除重复的数据
runoobdb=# SELECT DISTINCT name,address FROM company;
 name  |                       address
-------+------------------------------------------------------
 Teddy | Norway
 Paul  | California
 David | Texas
 Paul  | Houston
 Mark  | Rich-Mond
 Kim   | South-Hall
 haha  | 北京
 Allen | Texas
 James | Texas
 James | Norway
 James | Houston
(11 行记录)
--可以看到DISTINCT关键字应用于所有列，不仅仅是紧跟着它的第一个列
--所以当指定多个列时，除非指定的列完全相同，否则所有行都会被检索出来。