MySQL高級查詢技巧：分組、聚合、子查詢與分頁【MySQL系列】

本文將深入探討 MySQL 高級查詢技巧，重點講解 GROUP BY、HAVING、各種聚合函數、子查詢以及分頁查詢（LIMIT 語法）的使用。文章內容涵蓋實際應用中最常見的報表需求和分頁實現技巧，適合有一定 SQL 基礎的開發者進一步提升技能。

一、前置知識回顧

在進入高級部分之前，我們先簡要回顧一些 SQL 查詢的基本組成部分，便于后續內容的理解：

SELECT [字段列表]
FROM [表名]
WHERE [條件]
GROUP BY [分組字段]
HAVING [聚合條件]
ORDER BY [排序字段]
LIMIT [偏移量, 行數]

二、GROUP BY 分組查詢

2.1 基本語法

GROUP BY 用于將查詢結果按某個或某些字段進行分組。配合聚合函數（如 COUNT()、SUM()、AVG() 等）使用，可以實現對每個分組的統計。

示例：統計每個部門的員工數量

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

2.2 多字段分組

示例：統計每個部門中每個職位的員工數量

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

多字段分組適用于需要“交叉”維度分析的場景，比如不同區域+不同產品的銷售統計。

三、聚合函數詳解

聚合函數用于對一組數據進行計算，常用于 GROUP BY 分組后。

函數說明
COUNT()統計數量
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
GROUP_CONCAT()將組內字段連接為字符串

3.1 COUNT()

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

3.2 SUM()

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

3.3 GROUP_CONCAT()

SELECT department_id, GROUP_CONCAT(first_name) AS employee_names
FROM employees
GROUP BY department_id;

GROUP_CONCAT() 在報表中經常用于“拼接多個名稱為一列”，如列出參與某個項目的所有人名。

四、HAVING：對分組后的結果進行過濾

4.1 區別 WHERE 與 HAVING

  • WHERE 是對 原始數據 進行篩選
  • HAVING 是對 分組后的結果 進行篩選

4.2 示例：只顯示員工數大于5的部門

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

4.3 使用別名

雖然在 SELECT 中定義了別名 employee_count，但在 HAVING 中引用聚合函數更安全。

HAVING COUNT(*) > 5 -- 推薦
-- HAVING employee_count > 5 -- 有些版本不支持

五、子查詢的多種用法

子查詢是指嵌套在主查詢內部的 SELECT 查詢。可以出現在 SELECT、FROM、WHERE 等多個位置。

5.1 SELECT 中的子查詢

示例：查詢每位員工的平均工資差值

SELECT employee_id, salary,salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

5.2 WHERE 中的子查詢

示例：查詢工資高于公司平均值的員工

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

5.3 FROM 中的子查詢（內聯視圖）

用于將子查詢臨時當作一個“表”來使用。

SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id
) AS dept_avg
WHERE avg_salary > 10000;

5.4 IN/NOT IN 子查詢

示例：查找至少有一位員工的部門

SELECT department_id, department_name
FROM departments
WHERE department_id IN (SELECT DISTINCT department_id FROM employees
);

六、分頁查詢（LIMIT）詳解

在構建分頁接口或展示數據列表時，LIMIT 是非常關鍵的 SQL 工具。

6.1 LIMIT 基本用法

SELECT * FROM employees
LIMIT 10; -- 取前10條

6.2 LIMIT + OFFSET 用法

SELECT * FROM employees
LIMIT 10 OFFSET 20; -- 從第21條開始，取10條

等價寫法：

SELECT * FROM employees
LIMIT 20, 10;

6.3 用于分頁接口的實現

-- page = 3, pageSize = 10
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 20, 10;

分頁核心邏輯：LIMIT (page - 1) * pageSize, pageSize

七、常見報表需求實踐

以下為結合 GROUP BY、聚合函數、子查詢與分頁的常見報表查詢場景。

7.1 部門月度工資支出報表

SELECT department_id, DATE_FORMAT(hire_date, '%Y-%m') AS month,SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, month
ORDER BY department_id, month;

7.2 Top N 查詢（工資最高的前3名員工）

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

7.3 每個部門工資最高的員工（相關子查詢）

SELECT *
FROM employees e
WHERE salary = (SELECT MAX(salary)FROM employeesWHERE department_id = e.department_id
);

八、分頁優化技巧

分頁性能在大數據量下成為瓶頸，以下是常見優化方法。

8.1 使用覆蓋索引加速分頁

-- 僅查詢主鍵或索引字段
SELECT employee_id
FROM employees
ORDER BY hire_date DESC
LIMIT 100000, 10;

8.2 延遲關聯分頁

-- 第一步：查主鍵
SELECT employee_id
FROM employees
ORDER BY hire_date DESC
LIMIT 100000, 10;-- 第二步：再查詳情
SELECT * 
FROM employees
WHERE employee_id IN (…);

8.3 使用 ID 游標分頁（適合不斷增長的主鍵）

-- 假設上次最后一條ID是 120
SELECT * FROM employees
WHERE employee_id > 120
ORDER BY employee_id
LIMIT 10;

九、總結

技巧應用場景
GROUP BY數據分組統計、分類匯總
聚合函數報表、指標計算（如總數、平均值等）
HAVING分組結果過濾
子查詢復雜條件過濾、嵌套數據分析
LIMIT分頁列表、Top N 取值
分頁優化大數據分頁響應慢時的優化方案

實戰建議：

  1. 分組前過濾用 WHERE，分組后過濾用 HAVING
  2. 復雜統計盡量使用子查詢或視圖，保持主查詢簡潔
  3. 分頁查詢在大數據場景下需優化 LIMIT 的性能
  4. GROUP_CONCAT 適合小量數據展示，不宜用于大表

本項目適用于后臺管理系統、電商用戶中心、SaaS 用戶模塊等場景，特別適合開發者進行實戰演練與面試準備。

一、項目背景與需求概述

我們將構建一個基礎版的用戶管理系統，具備以下業務功能：

  • 用戶注冊與登錄
  • 用戶角色與權限分配
  • 日志記錄與用戶狀態追蹤
  • 多條件用戶查詢與分頁

涉及的核心業務對象包括：用戶、角色、權限、日志等。

二、數據庫建模與表結構設計

2.1 實體關系圖（ER圖）簡要說明

  • 一位用戶可以擁有多個角色（多對多）
  • 一個角色可以擁有多個權限（多對多）
  • 用戶與登錄日志是一對多關系

2.2 用戶表（users）

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,password VARCHAR(100) NOT NULL,email VARCHAR(100),status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:啟用',created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.3 角色表（roles）

CREATE TABLE roles (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,description VARCHAR(255)
);

2.4 權限表（permissions）

CREATE TABLE permissions (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL UNIQUE,code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于權限標識，如 user:view'
);

2.5 用戶-角色關聯表（user_role）

CREATE TABLE user_role (user_id INT,role_id INT,PRIMARY KEY (user_id, role_id),FOREIGN KEY (user_id) REFERENCES users(id),FOREIGN KEY (role_id) REFERENCES roles(id)
);

2.6 角色-權限關聯表（role_permission）

CREATE TABLE role_permission (role_id INT,permission_id INT,PRIMARY KEY (role_id, permission_id),FOREIGN KEY (role_id) REFERENCES roles(id),FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

2.7 登錄日志表（login_logs）

CREATE TABLE login_logs (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,ip_address VARCHAR(45),login_time DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
);

三、數據初始化腳本

3.1 插入初始角色與權限

INSERT INTO roles(name, description) VALUES ('admin', '系統管理員'), ('user', '普通用戶');INSERT INTO permissions(name, code) VALUES
('查看用戶', 'user:view'),
('新增用戶', 'user:create'),
('刪除用戶', 'user:delete');-- 分配權限給角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 擁有全部權限
(2, 1);                 -- user 僅能查看用戶

3.2 插入測試用戶

INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 為管理員
(2, 2); -- bob 為普通用戶

四、典型查詢場景實現

4.1 查詢所有啟用用戶及其角色

SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

4.2 查詢某用戶擁有的所有權限

SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';

4.3 查詢最近7天登錄日志

SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;

4.4 用戶分頁查詢（帶關鍵字搜索）

SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;

五、事務控制與一致性保障

在角色授權或用戶注冊等業務流程中，可以使用事務來確保數據完整性。

5.1 注冊用戶 + 分配默認角色（事務）

START TRANSACTION;INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默認賦普通角色COMMIT;

5.2 授權失敗時回滾

START TRANSACTION;-- 假設某權限不存在導致失敗
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);-- 失敗時回滾
ROLLBACK;

六、索引優化與執行分析

6.1 建議加索引字段

  • users.username：用于登錄驗證、搜索
  • login_logs.user_id：日志查詢
  • user_role.user_id / role_permission.role_id：JOIN 優化
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);

6.2 執行計劃分析

EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;

可查看索引是否使用、JOIN 類型、Rows 掃描數量等。

更多推薦【MySQL完整系列】：MySQL數據庫從0到拿捏系列

  1. MySQL數據庫零基礎入門教程：從安裝配置到數據查詢全掌握
    關鍵詞：安裝、登錄、客戶端、庫表基礎、簡單查詢

  2. MySQL數據表操作全指南：建表、修改、刪除一步到位
    關鍵詞：DDL語句、字段類型、主鍵/外鍵、約束、規范設計
    聚焦表結構的創建和維護，配合真實業務建表案例（如用戶表、訂單表）。

  3. MySQL增刪改查基礎教程：熟練掌握DML語句操作
    關鍵詞：INSERT、UPDATE、DELETE、SELECT、WHERE、ORDER BY
    實戰演練日常的數據庫操作命令，重點講解查詢語句的條件與排序。

  4. MySQL高級查詢技巧：分組、聚合、子查詢與分頁
    關鍵詞：GROUP BY、HAVING、聚合函數、LIMIT、子查詢
    向中級進階，涵蓋常見報表需求與分頁列表的查詢實現。

  5. MySQL多表查詢詳解：內連接、外連接、自連接通通搞懂
    關鍵詞：JOIN、INNER JOIN、LEFT JOIN、UNION、自連接
    深度講解表與表之間如何通過字段建立關聯并進行數據整合。

  6. MySQL索引與性能優化入門：讓查詢提速的秘密武器
    關鍵詞：索引原理、EXPLAIN、慢查詢、查詢優化
    開啟性能優化之路，適合準備應對數據量增長或面試的人。

  7. MySQL事務與鎖機制詳解：確保數據一致性的關鍵
    關鍵詞：事務四大特性、鎖類型、死鎖案例、隔離級別
    涉及電商、支付系統等對數據一致性要求高的業務場景。

  8. MySQL項目實戰演練：搭建用戶管理系統的完整數據庫結構
    關鍵詞：業務建模、表關系設計、數據初始化、查詢場景
    以實戰帶動知識回顧，模擬真實業務項目，整合前面所學內容。

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/bicheng/83209.shtml
繁體地址，請注明出處：http://hk.pswp.cn/bicheng/83209.shtml
英文地址，請注明出處：http://en.pswp.cn/bicheng/83209.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

現代 CSS 高階技巧：實現平滑內凹圓角的工程化實踐

現代 CSS 高階技巧：實現平滑內凹圓角的工程化實踐

通過 數學計算 CSS mask 復合遮罩 實現的真正幾何內凹效果： 背景是一張圖片，用來證明中間的凹陷是透明的。 完整代碼： app.js import FormPage from "./pages/formPage"; import "./App.css"; const App () > {re…
閱讀更多...
Qt不同布局添加不同控件

Qt不同布局添加不同控件

對于這種 不同布局添加不同控件 的情況，可以采用以下幾種簡化方法： 方法 1：使用 std::pair 或 std::tuple 配對（C++17 推薦） for (auto [layout, widget] : {std::pair{m_layoutMistakeCalibrate,
閱讀更多...
MySQL 事務解析

MySQL 事務解析

1. 事務簡介 事務（Transaction） 是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求，即這些操作要么同時成功，要么同時失敗。 經典案例&#xff1…
閱讀更多...
PyTorch中 torch.utils.data.DataLoader 的詳細解析和讀取點云數據示例

PyTorch中 torch.utils.data.DataLoader 的詳細解析和讀取點云數據示例

一、DataLoader 是什么？ torch.utils.data.DataLoader 是 PyTorch 中用于加載數據的核心接口，它支持： 批量讀取（batch）數據打亂（shuffle）多線程并行加載（num_workers）自…
閱讀更多...
在MDK中自動部署LVGL，在stm32f407ZGT6移植LVGL-8.4，運行demo，顯示label

在MDK中自動部署LVGL，在stm32f407ZGT6移植LVGL-8.4，運行demo，顯示label

在MDK中自動部署LVGL，在stm32f407ZGT6移植LVGL-8.4 一、硬件平臺二、實現功能三、移植步驟1、下載LVGL-8.42、MDK中安裝LVGL-8.43、配置RTE4、配置頭文件 lv_conf_cmsis.h5、配置lv_port_disp_template 四、添加心跳相關文件1、在STM32CubeMX中配置TIM7的參數2、使能…
閱讀更多...
德思特新聞 | 德思特與es:saar正式建立合作伙伴關系

德思特新聞 | 德思特與es:saar正式建立合作伙伴關系

德思特新聞 2025年5月9日，德思特科技有限公司（以下簡稱“德思特”）與德國嵌入式系統專家es:saar GmbH正式達成合作伙伴關系。此次合作旨在將 es:saar 的先進嵌入式開發與測試工具引入中國及亞太市場，助力本地客戶提升產品開發效率…
閱讀更多...
fork函數小解

fork函數小解

學了好久終于搞懂fork函數的一些作用 1. fork函數作用：用于創建新的子進程 這是fork最根本的功能，在父進程里創建新的子進程、 但是創建新的子進程之后呢？ 子進程和父進程的關系是什么樣的？ 為什么fork得到的子進程返回值為0&am…
閱讀更多...
opencv(C++) 變換圖像與形態學操作

opencv(C++) 變換圖像與形態學操作

文章目錄 使用腐蝕和膨脹圖像形態濾波器實現案例使用形態學濾波器對圖像進行開運算和閉運算實現案例在灰度圖像上應用形態學操作算子形態學梯度（Morphological Gradient）黑帽變換（Black-hat Transform）使用分水嶺算法進行圖像分割使用 MSER 提取顯著區域MSER 檢測與可視化使…
閱讀更多...
測試工程師學LangChain之promptTemplate 實戰筆記

測試工程師學LangChain之promptTemplate 實戰筆記

一、引言：大模型時代的測試自動化革命 2025 年，隨著大模型（如 DeepSeek）在自動化測試領域的廣泛應用，Prompt 編寫已成為測試工程師的核心技能之一。 為什么？ 大模型輸出的質量 90% 取決于輸入的 PromptLangChain 的 PromptTemplate 提供了參數化 Prompt 的標準化方案Ope…
閱讀更多...
CP2K 軟件介紹與使用指南

CP2K 軟件介紹與使用指南

CP2K 軟件介紹與使用指南 一、CP2K簡介 CP2K是一款開源的量子化學和固態物理模擬軟件包，主要用于原子尺度模擬，特別擅長以下領域： 第一性原理計算：基于密度泛函理論(DFT)的電子結構計算分子動力學(MD)：包括從頭算分…
閱讀更多...
npm、pnpm、yarn使用以及區別

npm、pnpm、yarn使用以及區別

npm 使用 安裝包&#xff1a;在項目目錄下&#xff0c;npm install <包名> 用于本地安裝包到 node_modules 目錄&#xff0c;并添加到 package.json 的 dependencies 中&#xff1b;npm install -g <包名> 用于全局安裝&#xff0c;適用于命令行工具等。初始化項目…
閱讀更多...
2025年北京市職工職業技能大賽第六屆信息通信行業網絡安全技能大賽復賽CTF部分WP-哥斯拉流量分析

2025年北京市職工職業技能大賽第六屆信息通信行業網絡安全技能大賽復賽CTF部分WP-哥斯拉流量分析

2025年北京市職工職業技能大賽第六屆信息通信行業網絡安全技能大賽復賽CTF部分WP-哥斯拉流量分析 一、流量分析 題目沒有任何提示，附件gzl.pcap 解題哥斯拉流量300多KB包很多，沒啥經驗只能挨個看回來之后又狠狠得擼了一把哥斯拉流量分析我這里用的是哥斯拉4.0.1 測試鏈接…
閱讀更多...
GitLab 18.0 正式發布，15.0 將不再受技術支持，須升級【六】

GitLab 18.0 正式發布，15.0 將不再受技術支持，須升級【六】

GitLab 是一個全球知名的一體化 DevOps 平臺&#xff0c;很多人都通過私有化部署 GitLab 來進行源代碼托管。極狐GitLab 是 GitLab 在中國的發行版&#xff0c;專門為中國程序員服務。可以一鍵式部署極狐GitLab。 學習極狐GitLab 的相關資料&#xff1a; 極狐GitLab 官網極狐…
閱讀更多...
React 項目中封裝 Excel 導入導出組件：技術分享與實踐

React 項目中封裝 Excel 導入導出組件：技術分享與實踐

文章目錄 前言一、為什么需要封裝 Excel 組件&#xff1f;二、技術選型三、核心實現1. 安裝依賴2. 封裝Excel導出3. 封裝導入組件 &#xff08;UploadExcel&#xff09; 總結 前言 在 React 項目中&#xff0c;處理 Excel 文件的導入和導出是常見的業務需求。無論是導出報表數…
閱讀更多...
RustDesk 搭建自建服務器并設置服務自啟動

RustDesk 搭建自建服務器并設置服務自啟動

目錄 0. 介紹 1. 事前準備 1.1 有公網 ip 的云服務器一臺 1.2 服務端部署包 1.3 客戶端安裝包 2. 部署 2.1 服務器環境準備 2.2 上傳服務端部署包 2.3 運行 pm2 3. 客戶端使用 3.1 安裝 3.2 配置 3.2.1 解鎖網絡設置 3.2.2 ID / 中級服務器 3.3 啟動效果 > …
閱讀更多...
基于Qt封裝數據庫基本增刪改查操作，支持多線程，并實現SQLite數據庫單例訪問

基于Qt封裝數據庫基本增刪改查操作，支持多線程，并實現SQLite數據庫單例訪問

抽出來的&#xff0c;直接用就行 頭文件CPP文件使用示例 頭文件 #ifndef DATABASECOMMON_H #define DATABASECOMMON_H/** 單例封裝SQLite通用操作&#xff0c;支持多線程調用&#xff1b;可擴展兼容其他數據庫&#xff0c;照著SysRunDatabase寫&#xff0c;并且重載openDataba…
閱讀更多...
AI筆記 - 網絡模型 - mobileNet

AI筆記 - 網絡模型 - mobileNet

網絡模型 mobileNet mobileNet V1網絡結構深度可分離卷積空間可分![在這里插入圖片描述](https://i-blog.csdnimg.cn/direct/aff06377feac40b787cfc882be7c6e5d.png) 參考 mobileNet V1 網絡結構 MobileNetV1可以理解為VGG中的標準卷積層換成深度可分離卷積 可分離卷積主要有…
閱讀更多...
第十五篇：MySQL 高級實戰項目：構建高可用、可觀測、性能優化一體化數據庫平臺

第十五篇：MySQL 高級實戰項目：構建高可用、可觀測、性能優化一體化數據庫平臺

本篇聚焦于如何基于 MySQL 構建一個真正面向生產環境的數據庫平臺&#xff0c;集成高可用、可觀測與性能調優三大核心能力&#xff0c;助力穩定、可擴展的系統運行。 一、項目背景與目標 在實際生產環境中&#xff0c;數據庫系統需要應對以下挑戰&#xff1a; 業務高速增長帶來…
閱讀更多...
華為OD機試真題——文件目錄大小（2025 A卷：100分）Java/python/JavaScript/C++/C語言/GO六種語言最佳實現

華為OD機試真題——文件目錄大小（2025 A卷：100分）Java/python/JavaScript/C++/C語言/GO六種語言最佳實現

2025 A卷 100分 題型 本文涵蓋詳細的問題分析、解題思路、代碼實現、代碼詳解、測試用例以及綜合分析； 并提供Java、python、JavaScript、C++、C語言、GO六種語言的最佳實現方式！ 2025華為OD真題目錄+全流程解析/備考攻略/經驗分享 華為OD機試真題《文件目錄大小》： 目錄 題…
閱讀更多...
qwen 2.5 并行計算機制：依靠 PyTorch 和 Transformers 庫的分布式能力

qwen 2.5 并行計算機制：依靠 PyTorch 和 Transformers 庫的分布式能力

qwen 2.5 并行計算機制：依靠 PyTorch 和 Transformers 庫的分布式能力 完整可運行代碼： import torch import torch.nn.functional as F from transformers
閱讀更多...
最新文章

Copyright @ 2022~2023