SELECT 进阶

进阶版的 SELECT 查询通常涉及多表关联、复杂逻辑处理、性能优化以及高级分析功能。以下是基于实际业务场景整理的进阶示例，涵盖了连接查询、子查询优化、窗口函数及条件逻辑等核心内容。

1. 多表联合查询 (JOINs)

在实际业务中数据往往分散在多个表中，合理使用 JOIN 是进阶查询的基础。

三表关联与空值处理

查询员工信息、所属部门及负责的项目，并处理可能存在的空值（如未分配部门或项目）。

SELECT
    e.emp_id AS '员工ID',
    e.emp_name AS '姓名',
    IFNULL(d.dept_name, '未分配') AS '部门',
    IFNULL(p.project_name, '暂无项目') AS '负责项目'
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON e.emp_id = p.lead_emp_id;

• 要点‌：使用 LEFT JOIN 确保即使员工没有部门或项目也能显示；使用 IFNULL 提升结果可读性。

自连接 (Self-Join)

用于处理层级关系，如查询员工及其直接上级。

SELECT
    sub.employee_name AS '下属',
    mgr.employee_name AS '上级经理'
FROM employees sub
JOIN employees mgr ON sub.manager_id = mgr.employee_id;

2、子查询优化与重构

子查询虽然直观，但在大数据量下容易成为性能瓶颈。进阶用法侧重于将其转化为更高效的 JOIN 或 EXISTS。

使用 LEFT JOIN 替代 NOT IN

查询“最近30天未登录”的用户。相比 NOT IN，LEFT JOIN ... IS NULL 通常能避免全表扫描和临时表生成，显著提升性能。

SELECT u.user_id, u.username
FROM users u
LEFT JOIN user_logins l
    ON u.user_id = l.user_id
    AND l.login_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
WHERE l.user_id IS NULL;

使用 EXISTS 进行存在性检查

查询“有高阶订单”的客户。EXISTS 在找到第一条匹配记录后即停止搜索，比 IN 更高效。

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.amount > 10000
);

派生表 (Derived Table)

当需要对子查询结果进行二次聚合或过滤时使用。

-- 先计算每个部门的平均薪资，再筛选出平均薪资高于公司整体平均水平的部门
SELECT dept_avg.dept_id, dept_avg.avg_sal
FROM (
    SELECT dept_id, AVG(salary) as avg_sal
    FROM employees
    GROUP BY dept_id
) AS dept_avg
WHERE dept_avg.avg_sal > (SELECT AVG(salary) FROM employees);

3、窗口函数 (Window Functions)

MySQL 8.0+ 引入了窗口函数，允许在不折叠行的情况下进行排名、累计计算等操作。

排名查询 (RANK / DENSE_RANK)

SELECT emp_name, dept_id, salary, rank_in_dept
FROM (
    SELECT
        emp_name,
        dept_id,
        salary,
        RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank_in_dept
    FROM employees
) ranked_employees
WHERE rank_in_dept <= 2;

累计求与移动平均

计算每日销售额的累计总和及近7日移动平均。

SELECT
    sale_date,
    daily_amount,
    SUM(daily_amount) OVER (ORDER BY sale_date) AS cumulative_total,
    AVG(daily_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

4、复杂条件逻辑 (CASE WHEN / IF)

在查询结果中动态生成字段值，实现业务逻辑前置。

动态标签分类

根据订单金额和状态自动打标。

SELECT
    order_id,
    amount,
    CASE
        WHEN amount > 5000 AND status = 'paid' THEN 'VIP大额已付'
        WHEN amount > 5000 THEN 'VIP大额待付'
        WHEN status = 'cancelled' THEN '已取消'
        ELSE '普通订单'
    END AS order_tag
FROM orders;

动态折扣计算

结合子查询结果动态计算最终价格。

SELECT
    o.order_id,
    o.amount,
    IF(
        (SELECT MAX(level) FROM customer_levels cl WHERE cl.customer_id = o.customer_id) > 3,
        o.amount * 0.9,  -- 高级会员9折
        o.amount * 0.95  -- 普通会员95折
    ) AS final_amount
FROM orders o;

5、性能优化最佳实践

在执行进阶查询时，需关注执行计划以确保效率。

· 使用 EXPLAIN 分析‌：

在复杂 SQL 前加上 EXPLAIN，重点关注 type 列（避免 ALL 全表扫描，争取 ref 或 range）和 Extra 列（避免 Using temporary 和 Using filesort）。

· 索引嵌套循环连接 (NLJ)‌：

在多表 JOIN 时，确保被驱动表（Inner Table）的关联字段上有索引。MySQL 优化器通常会选择小表作为驱动表，利用索引在被驱动表中快速查找匹配行，将复杂度从O(N×M)降低到O(N×logM)。

· 避免 SELECT *

尤其在多表连接时，只选取需要的列，减少网络传输开销和内存占用。

· 限制结果集‌：

如果只需要部分数据，务必使用 LIMIT，防止一次性加载过多数据导致内存溢出。