聚合函数如何与其他SQL语句配合使用

聚合函数在SQL中用于对一组值执行计算，并返回单个值。这些函数通常与GROUP BY子句一起使用，以便根据一个或多个列对数据进行分组，并对每个组应用聚合函数。以下是一些常见的聚合函数以及它们如何与其他SQL语句配合使用的示例：

1. COUNT()

• 用途：计算行数。
• 示例：

  SELECT COUNT(*) FROM employees;
  SELECT COUNT(department_id) FROM employees GROUP BY department_id;
  

2. SUM()

• 用途：计算数值列的总和。
• 示例：

  SELECT SUM(salary) FROM employees;
  SELECT SUM(salary) FROM employees GROUP BY department_id;
  

3. AVG()

• 用途：计算数值列的平均值。
• 示例：

  SELECT AVG(salary) FROM employees;
  SELECT AVG(salary) FROM employees GROUP BY department_id;
  

4. MAX()

• 用途：返回数值列的最大值。
• 示例：

  SELECT MAX(salary) FROM employees;
  SELECT MAX(salary) FROM employees GROUP BY department_id;
  

5. MIN()

• 用途：返回数值列的最小值。
• 示例：

  SELECT MIN(salary) FROM employees;
  SELECT MIN(salary) FROM employees GROUP BY department_id;
  

6. GROUP BY

• 用途：根据一个或多个列对结果集进行分组。
• 示例：

  SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;
  SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id;
  

7. HAVING

• 用途：过滤聚合函数的结果。
• 示例：

  SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id HAVING COUNT(*) > 10;
  SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 5000;
  

8. ORDER BY

• 用途：对结果集进行排序。
• 示例：

  SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id ORDER BY employee_count DESC;
  

综合示例

假设我们有一个employees表，包含以下列：employee_id, name, department_id, salary。

查询每个部门的员工数量和平均工资：

SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

查询工资高于平均工资的员工：

SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

查询每个部门的员工数量，并按员工数量降序排列：

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;

通过这些示例，你可以看到聚合函数如何与其他SQL语句配合使用，以实现各种数据分析和报告需求。