数据库高级操作——分组过滤

分组过滤

对分组结果进行过滤，必须使用HAVING子句，不能使用WHERE字句，WHERE字句是针对行的过滤。

• 单独使用HAVING字句

语法：

select column,group_function(column)
from table
[group by group_by_expression]
[having group_condition]
[order by column]

执行顺序：from>group by>having>select>order by

• HAVING字句与WHERE字句结合

语法：

select column,group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column]

执行顺序：from>where>group by>having>select>order by

例子：

示例Table：

1.JPG

• 分组GROUP BY

SELECT subject, COUNT(*) AS subject_count FROM score_table GROUP BY subject;

2.JPG

• WHERE + GROUP BY

SELECT subject, COUNT(*) AS subject_count FROM score_table WHERE score>80 GROUP BY subject;

3.JPG

• HAVING + GROUP BY

SELECT subject, COUNT(*) AS subject_count FROM score_table GROUP BY subject HAVING subject_count>2;

2.JPG

• WHERE + GROUP BY +HAVING

SELECT subject, COUNT(*) AS subject_count FROM score_table WHERE score>80 GROUP BY subject HAVING subject_count>=2;

5.JPG