MySQL?聚合函數(shù)、分組、聯(lián)合查詢詳解

更新時間：2026年01月23日 09:24:06 作者：bbq粉刷匠

本文介紹了MySQL中的聚合函數(shù)、分組查詢和聯(lián)合查詢,并通過多個示例題目進(jìn)行鞏固,聚合函數(shù)用于計算匯總統(tǒng)計值,分組查詢用于將數(shù)據(jù)分成組并應(yīng)用聚合函數(shù),聯(lián)合查詢用于從多個表中組合數(shù)據(jù),通過實例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

第一部分：聚合函數(shù) (Aggregate Functions)

聚合函數(shù)對一組值執(zhí)行計算，并返回單個值。它們通常與 GROUP BY 子句一起使用，用于對分組后的數(shù)據(jù)進(jìn)行匯總統(tǒng)計。MySQL 提供了多種聚合函數(shù)：

COUNT(): 計算行數(shù)。
- COUNT(*): 計算所有行的數(shù)量，包括包含 NULL 值的行。
- COUNT(column_name): 計算指定列中非 NULL 值的數(shù)量。
- COUNT(DISTINCT column_name): 計算指定列中不同（去重）非 NULL 值的數(shù)量。
示例題目 1.1: 假設(shè)有一個 students 表 (id, name, major, gpa)。查詢表中總共有多少學(xué)生。

SELECT COUNT(*) AS total_students FROM students;

示例題目 1.2: 查詢有多少個不同的專業(yè) (major) (假設(shè) major 可能重復(fù))。

SELECT COUNT(DISTINCT major) AS distinct_majors FROM students;

SUM(): 計算數(shù)值列的總和。忽略 NULL 值。
示例題目 1.3: 查詢所有學(xué)生的總 gpa 之和（假設(shè) gpa 是數(shù)值類型）。

SELECT SUM(gpa) AS total_gpa FROM students;

AVG(): 計算數(shù)值列的平均值。忽略 NULL 值。
計算平均值 $ \bar{x} $ 的公式為： $$ \bar{x} = \frac{\sum_{i=1}^{n} x_i}{n} $$
示例題目 1.4: 查詢所有學(xué)生的平均 gpa。

SELECT AVG(gpa) AS average_gpa FROM students;

MAX(): 返回指定列中的最大值。可用于數(shù)值、日期和字符串（按字符順序）。忽略 NULL 值。
示例題目 1.5: 查詢最高的 gpa 是多少。

SELECT MAX(gpa) AS highest_gpa FROM students;

MIN(): 返回指定列中的最小值?？捎糜跀?shù)值、日期和字符串（按字符順序）。忽略 NULL 值。
示例題目 1.6: 查詢最低的 gpa 是多少。
```
SELECT MIN(gpa) AS lowest_gpa FROM students;
```
- GROUP_CONCAT() (MySQL 特有): 將來自一個組的多個行中的字符串值連接成一個字符串。可以指定分隔符。
- GROUP_CONCAT(column_name): 默認(rèn)用逗號 , 連接。
- GROUP_CONCAT(column_name SEPARATOR ';'): 用分號 ; 連接。
- GROUP_CONCAT(DISTINCT column_name ORDER BY column_name): 去重并按指定列排序后連接。
示例題目 1.7: 按專業(yè) (major) 分組，列出每個專業(yè)的所有學(xué)生姓名（用逗號分隔）。

SELECT major, GROUP_CONCAT(name) AS student_names
FROM students
GROUP BY major;

示例題目 1.8: 按專業(yè)分組，列出每個專業(yè)所有不同的學(xué)生姓名（按字母順序排序，用分號分隔）。

SELECT major, GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ';') AS distinct_sorted_names
FROM students
GROUP BY major;

關(guān)鍵點總結(jié) (聚合函數(shù)):

聚合函數(shù)作用于一組行，返回一個單一的值。
它們通常用于 SELECT 列表中。
它們會忽略 NULL 值（COUNT(*) 除外）。
DISTINCT 可以在聚合函數(shù)內(nèi)部使用（如 COUNT(DISTINCT major)）來對列值去重后再計算。
單獨使用聚合函數(shù)時，它們作用于整個查詢結(jié)果集。

第二部分：分組查詢 (GROUP BY)

GROUP BY 子句用于根據(jù)一個或多個列對結(jié)果集進(jìn)行分組。然后，在每個分組上應(yīng)用聚合函數(shù)，得到每個組的匯總信息。

基本語法:

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition -- 可選，在分組 *前* 過濾行
GROUP BY column1 -- 指定分組依據(jù)的列
HAVING condition; -- 可選，在分組 *后* 過濾分組

執(zhí)行順序:

FROM: 確定數(shù)據(jù)來源。
WHERE: 根據(jù)條件過濾行 (在分組前)。
GROUP BY: 將剩余的行分成組。
HAVING: 根據(jù)條件過濾組 (在分組后，通常涉及聚合函數(shù))。
SELECT: 選擇要顯示的列（包括分組列和聚合結(jié)果）。
ORDER BY: 對最終結(jié)果排序。

分組依據(jù):

可以按單列分組：GROUP BY department_id
可以按多列分組：GROUP BY department_id, job_title (先按 department_id 分大組，再在每個大組內(nèi)按 job_title 分小組)。

示例題目 2.1: 有一個 orders 表 (order_id, customer_id, product_id, quantity, order_date)。統(tǒng)計每個客戶 (customer_id) 總共下了多少訂單 (COUNT(order_id))。

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id;

HAVING vs WHERE:
- WHERE: 在分組前過濾行。不能使用聚合函數(shù)。
- HAVING: 在分組后過濾組?？梢允褂镁酆虾瘮?shù)。

示例題目 2.2: 查詢平均成績 (AVG(gpa)) 大于 3.5 的專業(yè) (major)。

SELECT major, AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5; -- HAVING 過濾分組

示例題目 2.3: 查詢訂單表中，在 2023 年 (WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31') 下單次數(shù)超過 5 次 (COUNT(order_id) > 5) 的客戶 (customer_id)。

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' -- WHERE 在分組前過濾行
GROUP BY customer_id
HAVING COUNT(order_id) > 5; -- HAVING 在分組后過濾組

GROUP BY 與表達(dá)式: 分組依據(jù)可以是表達(dá)式的結(jié)果。

示例題目 2.4: 按年份 (YEAR(order_date)) 統(tǒng)計每年的總銷售額 (SUM(quantity * unit_price)，假設(shè) unit_price 存在)。

SELECT YEAR(order_date) AS order_year, SUM(quantity * unit_price) AS total_sales
FROM orders
JOIN products ON orders.product_id = products.product_id -- 假設(shè)有 products 表
GROUP BY YEAR(order_date);

關(guān)鍵點總結(jié) (GROUP BY):

GROUP BY 用于將數(shù)據(jù)分成邏輯組。
出現(xiàn)在 SELECT 列表中非聚合函數(shù)列，通常必須出現(xiàn)在 GROUP BY 子句中（或者被包含在聚合函數(shù)中）。
使用 HAVING 對分組結(jié)果進(jìn)行篩選，通常涉及聚合函數(shù)。
理解 WHERE (行過濾) 和 HAVING (組過濾) 的執(zhí)行時機和區(qū)別至關(guān)重要。

第三部分：聯(lián)合查詢 (JOIN)

聯(lián)合查詢用于從兩個或多個表（或視圖）中基于它們之間的相關(guān)列組合行。這是關(guān)系型數(shù)據(jù)庫的核心操作，用于整合分散在不同表中的數(shù)據(jù)。

MySQL 支持多種 JOIN 類型：

INNER JOIN (或簡寫為 JOIN):

返回兩個表中滿足連接條件的所有行。如果某行在其中一個表中沒有匹配的行，則該行不會出現(xiàn)在結(jié)果中。

語法:

SELECT columns
FROM table1
[INNER] JOIN table2
ON table1.common_column = table2.common_column;

示例題目 3.1: 有兩個表：employees (emp_id, emp_name, dept_id) 和 departments (dept_id, dept_name)。查詢所有員工及其所在部門的名稱。

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

LEFT JOIN (或 LEFT OUTER JOIN):

返回左表 (table1) 的所有行，即使在右表 (table2) 中沒有匹配的行。如果右表中沒有匹配的行，則結(jié)果中右表的部分將包含 NULL。

語法:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.common_column = table2.common_column;

示例題目 3.2: 查詢所有部門 (departments)，并列出屬于該部門的員工姓名（如果部門沒有員工，員工姓名顯示為 NULL）。

SELECT d.dept_name, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

RIGHT JOIN (或 RIGHT OUTER JOIN):

返回右表 (table2) 的所有行，即使在左表 (table1) 中沒有匹配的行。如果左表中沒有匹配的行，則結(jié)果中左表的部分將包含 NULL。

語法:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.common_column = table2.common_column;

示例題目 3.3: 查詢所有員工 (employees)，并列出他們所屬的部門名稱（如果員工未分配部門，部門名稱顯示為 NULL）。

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-- 注意：RIGHT JOIN 使用較少，通常用 LEFT JOIN 調(diào)整表順序?qū)崿F(xiàn)相同效果。例如上面的查詢等價于：
SELECT e.emp_name, d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id; -- 將 departments 作為左表

FULL JOIN (或 FULL OUTER JOIN):

返回左表和右表中的所有行。當(dāng)某行在另一個表中沒有匹配時，另一個表的部分將包含 NULL。
注意: MySQL 不直接支持 FULL JOIN。但可以通過組合 LEFT JOIN 和 RIGHT JOIN 并使用 UNION 來模擬實現(xiàn)。

語法 (模擬):

SELECT columns FROM table1 LEFT JOIN table2 ON ...
UNION
SELECT columns FROM table1 RIGHT JOIN table2 ON ...;

示例題目 3.4: 查詢所有部門和所有員工，列出所有可能的組合（有歸屬關(guān)系的顯示關(guān)系，無歸屬關(guān)系的顯示 NULL）。

SELECT d.dept_id, d.dept_name, e.emp_id, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
UNION
SELECT d.dept_id, d.dept_name, e.emp_id, e.emp_name
FROM departments d
RIGHT JOIN employees e ON d.dept_id = e.dept_id
WHERE d.dept_id IS NULL; -- 排除 LEFT JOIN 部分已經(jīng)包含的行
-- 更簡潔的模擬（假設(shè) dept_id 在 departments 和 employees 中都是非 NULL 且唯一）:
SELECT d.dept_id, d.dept_name, e.emp_id, e.emp_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
UNION
SELECT d.dept_id, d.dept_name, e.emp_id, e.emp_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

CROSS JOIN:

返回兩個表的笛卡爾積，即左表的每一行與右表的每一行組合。不使用 ON 子句（或者使用無效的連接條件）。結(jié)果集的行數(shù)是左表行數(shù)乘以右表行數(shù)。

語法:

SELECT columns
FROM table1
CROSS JOIN table2;
-- 或者 (不推薦，易與 INNER JOIN 混淆)
SELECT columns
FROM table1, table2;

示例題目 3.5: 生成一個包含所有顏色 (colors) 和所有尺碼 (sizes) 組合的列表。

SELECT color_name, size_name
FROM colors
CROSS JOIN sizes;

SELF JOIN:

一個表與其自身進(jìn)行連接。通常用于處理層次結(jié)構(gòu)（如員工和經(jīng)理）、比較同一表中的行。
需要使用表別名來區(qū)分同一個表的兩個實例。

語法:

SELECT a.column, b.column
FROM table_name a
JOIN table_name b ON a.common_column = b.common_column
WHERE condition; -- 通常需要額外條件來排除無效連接（如 a.id != b.id）

示例題目 3.6: 在 employees 表中，每個員工 (emp_id) 有一個經(jīng)理 (manager_id)，經(jīng)理也是員工。查詢每個員工及其經(jīng)理的姓名。

SELECT e.emp_name AS employee_name, m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id; -- 使用 LEFT JOIN 處理沒有經(jīng)理的情況

示例題目 3.7: 在 products 表中，找出價格 (price) 相同的不同產(chǎn)品 (product_id)。

SELECT a.product_id AS product_a, b.product_id AS product_b, a.price
FROM products a
INNER JOIN products b ON a.price = b.price
WHERE a.product_id < b.product_id; -- 避免 (A, B) 和 (B, A) 重復(fù)，以及 (A, A)

多表連接:

可以連接兩個以上的表。

語法:

SELECT ...
FROM table1
JOIN table2 ON condition1
JOIN table3 ON condition2
...
[WHERE ...]
[GROUP BY ...]
[HAVING ...];

示例題目 3.8: 有三個表：orders (order_id, customer_id, order_date), order_details (order_id, product_id, quantity), products (product_id, product_name, price)。查詢訂單號、客戶ID、產(chǎn)品名稱、購買數(shù)量和總金額 (quantity * price)。

SELECT o.order_id, o.customer_id, p.product_name, od.quantity, (od.quantity * p.price) AS total_amount
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;

關(guān)鍵點總結(jié) (JOIN):

JOIN 用于組合多個表中的數(shù)據(jù)。
理解不同 JOIN 類型（INNER, LEFT, RIGHT, CROSS, SELF）的語義和行為差異。
使用 ON 子句指定連接條件（通常是等值連接）。
使用表別名簡化多表查詢，尤其是在 SELF JOIN 時。
注意 NULL 值在 LEFT/RIGHT JOIN 中的處理。

第四部分：綜合題目鞏固

現(xiàn)在，我們將聚合函數(shù)、分組查詢和聯(lián)合查詢結(jié)合起來，解決更復(fù)雜的問題。

題目 4.1: 在 orders 和 order_details、products 表結(jié)構(gòu)下（假設(shè) products 表有 category_id，categories 表有 category_id, category_name）：查詢每個產(chǎn)品類別 (category_name) 在 2023 年的總銷售額 (SUM(quantity * price))，并按銷售額從高到低排序。

SELECT c.category_name, SUM(od.quantity * p.price) AS total_sales
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE YEAR(o.order_date) = 2023 -- 過濾年份
GROUP BY c.category_name -- 按類別分組
ORDER BY total_sales DESC; -- 按銷售額降序排序

題目 4.2: 在 employees (emp_id, emp_name, salary, dept_id) 和 departments (dept_id, dept_name) 表下：查詢每個部門 (dept_name) 的平均工資 (AVG(salary))，并且只顯示平均工資高于公司整體平均工資的部門。

SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees); -- 子查詢獲取公司整體平均工資

題目 4.3: 在 students (student_id, student_name), courses (course_id, course_name), enrollments (student_id, course_id, grade) 表下：查詢每個學(xué)生 (student_name) 選修的課程數(shù)量 (COUNT(course_id)) 和平均成績 (AVG(grade))，并按平均成績降序排列。

SELECT s.student_name, COUNT(e.course_id) AS course_count, AVG(e.grade) AS avg_grade
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id -- 使用 LEFT JOIN 確保沒選課的學(xué)生也被列出 (count=0, avg=null)
GROUP BY s.student_id, s.student_name -- 按學(xué)生分組 (通常按主鍵分組更安全)
ORDER BY avg_grade DESC; -- NULL 在降序排列中通常排在最后

題目 4.4: 在 orders (order_id, customer_id, order_date), customers (customer_id, customer_name, country), order_details (order_id, product_id, quantity), products (product_id, product_name) 表下：查詢 2023 年每個國家 (country) 的每個客戶 (customer_name) 購買的不同產(chǎn)品數(shù)量 (COUNT(DISTINCT product_id)) 和總購買金額 (SUM(quantity * price))，僅列出總金額超過 1000 的客戶。

SELECT c.country, c.customer_name,
       COUNT(DISTINCT od.product_id) AS distinct_products,
       SUM(od.quantity * p.price) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE YEAR(o.order_date) = 2023
GROUP BY c.country, c.customer_id, c.customer_name -- 按國家和客戶分組
HAVING total_spent > 1000
ORDER BY c.country, total_spent DESC;

題目 4.5: 在 employees (emp_id, emp_name, manager_id) 表下：查詢每個經(jīng)理 (manager_name) 及其直接下屬的數(shù)量 (COUNT(emp_id)) 和下屬的平均工資 (假設(shè)有 salary 列)，列出下屬數(shù)量最多的前 5 位經(jīng)理。

SELECT m.emp_name AS manager_name,
       COUNT(e.emp_id) AS direct_reports_count,
       AVG(e.salary) AS avg_subordinate_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id -- 連接自身，e 是下屬，m 是經(jīng)理
GROUP BY m.emp_id, m.emp_name -- 按經(jīng)理分組
ORDER BY direct_reports_count DESC
LIMIT 5;

總結(jié)

聚合函數(shù) (COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT): 用于計算匯總統(tǒng)計值。
分組查詢 (GROUP BY): 用于將數(shù)據(jù)分成組，以便在每個組上應(yīng)用聚合函數(shù)。HAVING 用于對分組結(jié)果進(jìn)行篩選。
聯(lián)合查詢 (JOIN): 用于組合多個相關(guān)表中的數(shù)據(jù)。掌握 INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, SELF JOIN 的用法和區(qū)別是核心。
綜合運用: 在實際查詢中，通常需要組合使用這些技術(shù)。理解 SQL 語句的執(zhí)行順序 (FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT) 對于編寫正確高效的查詢至關(guān)重要。