SQL 面试攻略：从普通到困难的副本通关之旅-51CTO.COM

本文汇总了一些 SQL 面试中经典且具有代表性的题目，涵盖普通和困难模式，帮助读者熟悉一些常考的 SQL 问题。每道题均附有详细解答，提供示例代码和解析。

示例表结构

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    manager_id INT,
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

普通模式

1. 计算每个部门的平均工资，并只显示平均工资超过 6000 的部门

解答：

SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000;

解析：这个查询使用了 GROUP BY 子句进行分组，AVG () 函数计算平均值，HAVING 子句用于过滤分组后的结果。

2. 查询 employees 表中每个部门工资最高的员工姓名和工资

解答：

SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
);

解析：

通过子查询找出每个部门的最高工资。
使用外层查询关联部门和最高工资对应的员工。

3. 找出工资高于公司平均工资的所有员工

解答：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

解析：这个查询使用了子查询来计算平均工资，然后在主查询中使用这个结果进行比较。

4. 查询两个表 employees 和 departments，展示每位员工的姓名和所属部门的名称

解答：

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

解析：

使用 INNER JOIN 连接两张表。
考查多表关联查询。
困难模式

5. 查询 employees 表中所有重复的姓名及其出现次数

解答：

SELECT name, COUNT(*) AS name_count
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

解析：

使用 GROUP BY 对名字分组。
HAVING 子句筛选分组后的数据。
考查聚合和过滤的结合使用。

6. 查询连续三个月工资增长的员工姓名

示例表结构：

CREATE TABLE salaries (
    employee_id INT,
    month DATE,
    salary DECIMAL(10, 2),
    PRIMARY KEY (employee_id, month)
);

解答：

SELECT DISTINCT s1.employee_id
FROM salaries s1
JOIN salaries s2 ON s1.employee_id = s2.employee_id AND DATE_ADD(s1.month, INTERVAL 1 MONTH) = s2.month
JOIN salaries s3 ON s2.employee_id = s3.employee_id AND DATE_ADD(s2.month, INTERVAL 1 MONTH) = s3.month
WHERE s1.salary < s2.salary AND s2.salary < s3.salary;

解析：

通过自连接比较同一员工在连续月份的工资。
使用 DATE_ADD 处理日期计算。
考查自连接和复杂条件查询。

7. 查询员工的累计工资排名（按工资降序排列）

解答：

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

解析：

使用窗口函数 RANK() 实现排名。
考查窗口函数的使用。

8. 对于每个部门，查找工资排名前三的员工

解答：

SELECT name, department_id, salary
FROM (
    SELECT name, department_id, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
    FROM employees
) ranked
WHERE rank <= 3;

解析：这个查询使用了窗口函数 DENSE_RANK () 来为每个部门的员工根据工资进行排名，然后在外部查询中筛选出排名前三的记录。

9. 显示所有员工及其所有上级管理者的姓名

解答：

WITH RECURSIVE emp_hierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT e.name as employee, GROUP_CONCAT(m.name ORDER BY m.id) as managers
FROM emp_hierarchy e
LEFT JOIN emp_hierarchy m ON e.id != m.id AND e.id = m.manager_id
GROUP BY e.id, e.name;

解析：这是一个递归公共表表达式（CTE）查询，用于处理层级数据。它首先找到顶级管理者，然后递归地找到所有下属。

结语

SQL 面试题往往注重以下能力：

数据查询、过滤与分组：如 SELECT、WHERE、GROUP BY。
多表操作：如 JOIN、子查询。
高级特性：如窗口函数、自连接。