1. GROUP BY的定义
在SQL查询中,GROUP BY是一个用于对结果集进行分组的语句。它按照指定的列对数据进行分组,并将这些分组看作一个整体,而不是单独的行。
在使用GROUP BY时,我们需要指定一个或多个用于分组的列,并在SELECT语句中使用聚合函数计算各个分组的统计数据,如总数、平均值、最大值、最小值等。
下面是一个使用GROUP BY的简单示例:
SELECT department, MAX(salary)
FROM employees
GROUP BY department;
上述语句将会按照department
列对employees
表进行分组,并计算每个分组中salary
列的最大值。
2. GROUP BY的语法
GROUP BY语句的基本语法如下:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;
其中:
column1, column2:需要分组的列。
aggregate_function(column3):对column3
列进行聚合计算的函数,如COUNT
、SUM
、AVG
、MIN
、MAX
等。
table_name:要查询的表名。
condition:查询条件。
HAVING condition:分组后的过滤条件,用于过滤分组后的数据。
ORDER BY:对结果进行排序。
2.1 GROUP BY的例子
现在让我们通过一个列子来理解GROUP BY的用法。我们考虑一个存储销售订单的表orders
,该表包含以下列:
order_id
:订单ID
customer_id
:客户ID
order_date
:订单日期
order_amount
:订单金额
现在我们要统计每个客户的订单总金额,可以使用如下SQL语句:
SELECT customer_id, SUM(order_amount)
FROM orders
GROUP BY customer_id;
上述语句将按照customer_id
列对orders
表进行分组,计算每个分组中order_amount
列的总数。
3. GROUP BY的实际应用
3.1 统计每个部门的人数和平均工资
假设我们有如下员工表:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
salary INT NOT NULL,
department VARCHAR(50) NOT NULL
);
INSERT INTO employees (emp_id, emp_name, salary, department)
VALUES
(1, 'John', 5000, 'Sales'),
(2, 'Jane', 6000, 'Sales'),
(3, 'Bob', 4000, 'IT'),
(4, 'Mary', 5500, 'IT'),
(5, 'Mike', 7000, 'Marketing');
我们可以使用如下SQL语句来统计每个部门的人数和平均工资:
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
上述语句将返回以下结果:
department | emp_count | avg_salary |
---|---|---|
IT | 2 | 4750.0000 |
Marketing | 1 | 7000.0000 |
Sales | 2 | 5500.0000 |
从上表可以看出,IT部门有2名员工,平均工资为4750元;Marketing部门有1名员工,平均工资为7000元;Sales部门有2名员工,平均工资为5500元。
3.2 查询每个学生的选课数量
假设我们有如下学生表和选课表:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
gender VARCHAR(10) NOT NULL,
birthday DATE NOT NULL
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL,
teacher VARCHAR(50) NOT NULL
);
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
enroll_date DATE NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
INSERT INTO students (student_id, student_name, gender, birthday)
VALUES
(1, 'John', 'Male', '1990-01-01'),
(2, 'Jane', 'Female', '1991-02-02'),
(3, 'Bob', 'Male', '1992-03-03'),
(4, 'Mary', 'Female', '1993-04-04'),
(5, 'Mike', 'Male', '1994-05-05');
INSERT INTO courses (course_id, course_name, teacher)
VALUES
(1, 'Math', 'Mr. White'),
(2, 'English', 'Ms. Brown'),
(3, 'History', 'Mr. Black'),
(4, 'Physics', 'Ms. Green');
INSERT INTO enrollments (enrollment_id, student_id, course_id, enroll_date)
VALUES
(1, 1, 1, '2018-09-01'),
(2, 1, 2, '2018-09-02'),
(3, 2, 2, '2018-09-05'),
(4, 2, 3, '2018-09-10'),
(5, 2, 4, '2018-09-15'),
(6, 3, 1, '2018-09-20'),
(7, 4, 2, '2018-09-25'),
(8, 5, 1, '2018-09-30');
我们可以使用如下SQL语句查询每个学生的选课数量:
SELECT s.student_name, COUNT(*) AS course_count
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id;
上述语句将返回以下结果:
student_name | course_count |
---|---|
John | 2 |
Jane | 3 |
Bob | 1 |
Mary | 1 |
Mike | 1 |
从上表可以看出,学生John选了2门课程,Jane选了3门课程,Bob、Mary、Mike各选了1门课程。
4. GROUP BY和HAVING的区别
GROUP BY和HAVING都是用于对SQL查询的结果进行分组、筛选。
GROUP BY:按照指定的列对数据进行分组,用于聚合计算。
HAVING:用于分组后对分组数据进行筛选,只返回满足条件的分组。
下面是一个使用GROUP BY和HAVING筛选的例子:
SELECT s.department, AVG(s.salary) AS avg_salary
FROM employees s
GROUP BY s.department
HAVING AVG(s.salary) > 5000;
上述语句将按照department
列对employees
表进行分组,并计算每个部门的平均工资,然后返回那些平均工资大于5000元的部门。