聚合函数如何筛选数据

在数据库查询中，聚合函数用于对一组值执行计算，并返回单个值。这些函数通常用于筛选和汇总数据。以下是一些常用的聚合函数及其用法：

1. COUNT()

• 功能：计算行数。
• 用法：SELECT COUNT(*) FROM table_name;
• 筛选：可以结合 WHERE 子句来筛选特定条件的行。

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

2. SUM()

• 功能：计算数值列的总和。
• 用法：SELECT SUM(column_name) FROM table_name;
• 筛选：同样可以使用 WHERE 子句。

SELECT SUM(salary) FROM employees WHERE department = 'Sales';

3. AVG()

• 功能：计算平均值。
• 用法：SELECT AVG(column_name) FROM table_name;
• 筛选：使用 WHERE 子句。

SELECT AVG(salary) FROM employees WHERE department = 'Sales';

4. MAX() 和 MIN()

• 功能：分别返回最大值和最小值。
• 用法：SELECT MAX(column_name) FROM table_name; 和 SELECT MIN(column_name) FROM table_name;
• 筛选：使用 WHERE 子句。

SELECT MAX(salary) FROM employees WHERE department = 'Sales';
SELECT MIN(salary) FROM employees WHERE department = 'Sales';

5. GROUP BY

• 功能：将结果集按一个或多个列进行分组。
• 用法：SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;
• 筛选：可以在 GROUP BY 之前使用 WHERE 子句进行筛选。

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

6. HAVING

• 功能：用于筛选分组后的结果。
• 用法：SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition;
• 筛选：HAVING 子句通常与 GROUP BY 一起使用。

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

示例

假设有一个 employees 表，包含以下列：id, name, department, salary。

查询销售部门的员工总数：

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

查询销售部门的平均工资：

SELECT AVG(salary) FROM employees WHERE department = 'Sales';

查询每个部门的员工总数和平均工资，并筛选出平均工资大于5000的部门：

SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

通过这些聚合函数和筛选条件，你可以有效地从数据库中提取和分析数据。