构建基础数据模型

在执行高级查询之前，需要先构建具备关联关系的数据表。以下是重构后的业务表结构及初始化数据脚本：

1. 组织架构表（sys_dept）

CREATE TABLE sys_dept (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    parent_dept_id INT DEFAULT NULL,
    CONSTRAINT fk_parent_dept FOREIGN KEY (parent_dept_id) REFERENCES sys_dept(dept_id)
);

INSERT INTO sys_dept (dept_id, dept_name, parent_dept_id) VALUES
(10, '研发中心', NULL),
(20, '人力资源部', NULL),
(30, '前端组', 10),
(40, '后端组', 10);

2. 员工信息表（sys_employee）

CREATE TABLE sys_employee (
    emp_no INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    base_salary DECIMAL(12, 2) NOT NULL,
    belong_dept INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (belong_dept) REFERENCES sys_dept(dept_id)
);

INSERT INTO sys_employee (emp_no, full_name, base_salary, belong_dept) VALUES
(101, '张三', 15000.00, 30),
(102, '李四', 18000.00, 30),
(103, '王五', 22000.00, 40),
(104, '赵六', 12000.00, 20);

3. 客户档案表（biz_client）与交易订单表（biz_order）

CREATE TABLE biz_client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    last_order_date DATE
);

CREATE TABLE biz_order (
    order_no INT PRIMARY KEY,
    client_id INT,
    created_at DATE,
    current_state VARCHAR(50),
    CONSTRAINT fk_order_client FOREIGN KEY (client_id) REFERENCES biz_client(client_id)
);

INSERT INTO biz_client (client_id, client_name) VALUES 
(1, '甲企业'), 
(2, '乙企业');

INSERT INTO biz_order (order_no, client_id, created_at, current_state) VALUES
(1001, 1, '2023-05-10', '待支付'),
(1002, 1, '2023-06-15', '已完成'),
(1003, 2, '2023-05-20', '待支付');

MySQL 高级查询实战场景

1. 相关子查询（Correlated Subquery）

通过嵌套查询获取每个部门中薪资最高的员工明细。外部查询与内部查询通过部门ID产生联动。

SELECT d.dept_name, e.full_name, e.base_salary
FROM sys_dept d
INNER JOIN sys_employee e ON d.dept_id = e.belong_dept
WHERE e.base_salary = (
    SELECT MAX(sub_e.base_salary)
    FROM sys_employee sub_e
    WHERE sub_e.belong_dept = e.belong_dept
);

2. 集合操作（UNION 与 UNION ALL）

合并不同数据源的结果集。UNION 会自动去除重复记录，而 UNION ALL 则保留所有行，执行效率更高。

-- 合并本月活跃用户与历史VIP用户名单（自动去重）
SELECT user_id FROM active_users_monthly
UNION
SELECT user_id FROM vip_users_history;

-- 保留重复记录的合并操作
SELECT user_id FROM active_users_monthly
UNION ALL
SELECT user_id FROM vip_users_history;

3. 多表内连接（INNER JOIN）

提取所有已成交订单及其归属客户的名称，仅返回两个表中匹配成功的记录。

SELECT o.order_no, c.client_name, o.created_at
FROM biz_order o
INNER JOIN biz_client c ON o.client_id = c.client_id
WHERE o.current_state = '已完成';

4. 自关联查询（Self Join）

将同一张表视为两张不同的表进行关联，常用于处理树状层级结构，如展示子部门及其对应的上级部门。

SELECT 
    parent.dept_name AS superior_dept, 
    child.dept_name AS sub_dept
FROM sys_dept parent
LEFT JOIN sys_dept child ON child.parent_dept_id = parent.dept_id;

5. 分组聚合与条件过滤（GROUP BY & HAVING）

使用聚合函数统计数据，并通过 HAVING 子句对分组后的结果进行二次筛选。

-- 计算各部门的薪资总支出
SELECT belong_dept, SUM(base_salary) AS total_payroll
FROM sys_employee
GROUP BY belong_dept;

-- 筛选出员工人数达到或超过2人的部门
SELECT belong_dept, COUNT(emp_no) AS headcount
FROM sys_employee
GROUP BY belong_dept
HAVING headcount >= 2;

6. 窗口函数（Window Functions）

利用 OVER() 子句在不减少原表行数的情况下进行聚合或排名计算。DENSE_RANK() 可保证排名连续。

-- 在部门内部按薪资降序生成密集排名
SELECT 
    emp_no, 
    base_salary, 
    belong_dept,
    DENSE_RANK() OVER (PARTITION BY belong_dept ORDER BY base_salary DESC) AS salary_rank
FROM sys_employee;

7. 复杂多表更新（Multi-table UPDATE）

在更新数据时关联多张表。以下示例根据订单表中的最新交易时间，批量修正客户档案表中的最后下单日期。

UPDATE biz_client c
INNER JOIN (
    SELECT client_id, MAX(created_at) AS latest_order
    FROM biz_order
    GROUP BY client_id
) recent_orders ON c.client_id = recent_orders.client_id
SET c.last_order_date = recent_orders.latest_order;