sql语句中GROUP BY 和 HAVING的使用 count()

- 日理万妓 2021-09-11 04:10 363阅读 0赞

在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数：聚合函数，   
例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。   
  
SELECT SUM(population) FROM bbc   
  
这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有   
国家的总人口数。

having是分组（group by）后的筛选条件，分组后的数据组内再筛选 where则是在分组前筛选

通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。   
当你指定 GROUP BY region 时， 属于同一个region（地区）的一组数据将只能返回一行值．   
也就是说，表中所有除region（地区）外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值．   
  
HAVING子句可以让我们筛选成组后的各组数据．   
WHERE子句在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前．   
而 HAVING子句在聚合后对组记录进行筛选。   
  
让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句，还采用第三节介绍的bbc表。   
  
SQL实例：   
  
一、显示每个地区的总人口数和总面积．   
SELECT region, SUM(population), SUM(area)  
FROM bbc\# \`& e4 k' X\* n1 v% ?+ |  
GROUP BY region  
 先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。\# B\* i' z  \`, \}\* S, E5 i   
  
二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。   
SELECT region, SUM(population), SUM(area)7 \]; Z& I! t% i  
FROM bbc8 F4 w2 v( P- f  
GROUP BY region  
HAVING SUM(area)>1000000\# y" P  z. O7 D9 \`\# X  
在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。  
相反，HAVING子句可以让我们筛选成组后的各组数据

三、查询CUSTOMER 和ORDER表中用户的订单数

select c.name, count(order\_number) as count from orders o,customer c where c.id=o.customer\_id group by customer\_id;

\+--------+-------+  
| name   | count |  
\+--------+-------+  
| d      |     9 |  
| cc     |     6 |  
| 菩提子 |     1 |  
| cccccc |     2 |  
\+--------+-------+

增加HAVING过滤

select c.name, count(order\_number) as count from orders o,customer c where c.id=o.customer\_id group by customer\_id having count(order\_number)>5;

\+------+-------+  
| name | count |  
\+------+-------+  
| d    |     9 |  
| cc   |     6 |  
\+------+-------+

四、我在多举一些例子

SQL> select \* from sc;

SNO PNO        GRADE  
\---------- ----- ----------  
         1 YW             95  
         1 SX              98  
         1 YY             90  
         2 YW            89  
         2 SX             91  
         2 YY             92  
         3 YW            85  
         3 SX             88  
         3 YY             96  
         4 YW            95  
         4 SX             89

SNO PNO        GRADE  
\---------- ----- ----------  
         4    YY            88

这个表所描述的是4个学生对应每科学习成绩的记录，其中SNO（学生号）、PNO（课程名）、GRADE（成绩）。

1、显示90分以上学生的课程名和成绩

//这是一个简单的查询，并没有使用分组查询

SQL> select sno,pno,grade from sc where grade>=90;

SNO PNO        GRADE  
\---------- ----- ----------  
         1 YW            95  
         1 SX             98  
         1 YY             90  
         2 SX             91  
         2 YY             92  
         3 YY             96  
         4 YW            95

已选择7行。

2、显示每个学生的成绩在90分以上的各有多少门

//进行分组显示，并且按照where条件之后计数

SQL> select sno,count(\*) from sc where grade>=90 group by sno;

SNO   COUNT(\*)  
\---------- ----------  
         1          3  
         2          2  
         4          1  
         3          1

3、这里我们并没有使用having语句，接下来如果我们要评选三好学生，条件是至少有两门课程在90分以上才能有资格，列出有资格的学生号及90分以上的课程数。

//进行分组显示，并且按照where条件之后计数，在根据having子句筛选分组

SQL> select sno,count(\*) from sc where grade>=90 group by sno having count(\*)>=2；

SNO   COUNT(\*)  
\---------- ----------  
         1          3  
         2          2

这个结果是我们想要的，它列出了具有评选三好学生资格的学生号，跟上一个例子比较之后，发现这是在分组后进行的子查询。

4、学校评选先进学生，要求平均成绩大于90分的学生都有资格，并且语文课必须在95分以上，请列出有资格的学生

//实际上，这个查询先把语文大于95分的学生号提取出来，之后求平均值，分组显示后根据having语句选出平均成绩大于90的