SQL中的连接，分组，聚合函数详解

在 SQL 中，连接（JOIN）、分组（GROUP BY） 和 聚合函数（Aggregate Functions） 是处理复杂查询的核心功能。以下是对这些功能的详细说明和示例：

 

1. 连接（JOIN）
连接用于从多个表中组合数据。常见的连接类型包括：

【INNER JOIN】
返回两个表中匹配的记录。
示例：
sql
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

【LEFT JOIN】
返回左表中的所有记录，即使右表中没有匹配的记录。
示例：
sql
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

【RIGHT JOIN】
返回右表中的所有记录，即使左表中没有匹配的记录。
示例：
sql
SELECT users.name, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

【FULL OUTER JOIN】
返回两个表中的所有记录（MySQL 不支持，可用 `UNION` 替代）。
示例：
sql
SELECT users.name, orders.amount
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

【CROSS JOIN】
返回两个表的笛卡尔积（所有可能的组合）。
示例：
sql
SELECT users.name, orders.amount
FROM users
CROSS JOIN orders;

 

2. 分组（GROUP BY）
分组用于将数据按某列或某些列的值进行分组，通常与聚合函数一起使用。

基本语法
sql
SELECT column1, column2, aggregate_function(column3)
FROM table
GROUP BY column1, column2;

示例
sql
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

 

3. 聚合函数（Aggregate Functions）
聚合函数用于对一组值进行计算并返回单个值。常见的聚合函数包括：

【COUNT】
统计行数。
示例：
sql
SELECT COUNT(*) FROM users;

【SUM】
计算某列的总和。
示例：
sql
SELECT SUM(amount) FROM orders;

【AVG】
计算某列的平均值。
示例：
sql
SELECT AVG(age) FROM users;

【MIN】
查找某列的最小值。
示例：
sql
SELECT MIN(age) FROM users;

【MAX】
查找某列的最大值。
示例：
sql
SELECT MAX(age) FROM users;

 

4. 分组与聚合函数的结合使用
分组和聚合函数通常一起使用，以对分组后的数据进行统计。

示例 1：按部门统计员工数量
sql
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

示例 2：按部门统计平均工资
sql
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

示例 3：按部门统计最高和最低工资
sql
SELECT department, MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM employees
GROUP BY department;

 

5. 分组后的过滤（HAVING）
`HAVING` 用于对分组后的结果进行过滤，类似于 `WHERE`，但用于分组后的数据。

示例：筛选出员工数量大于 10 的部门
sql
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

 

6. 连接、分组和聚合函数的综合应用
在实际查询中，连接、分组和聚合函数可以结合使用，以处理复杂的业务逻辑。

示例：统计每个用户的订单总金额
sql
SELECT users.name, SUM(orders.amount) AS total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id
GROUP BY users.name;

示例：统计每个部门的平均工资，并筛选出平均工资大于 5000 的部门
sql
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

 

总结
- 连接（JOIN）：用于从多个表中组合数据。
- 分组（GROUP BY）：用于将数据按某列或某些列的值进行分组。
- 聚合函数：用于对一组值进行计算并返回单个值。
- HAVING：用于对分组后的结果进行过滤。

掌握这些功能可以帮助你编写复杂的 SQL 查询，从而更好地完成数据分析和验证任务。