10道常考SQL笔试题(11-20)

11.获取每个部门工资第二高的员工

题目：

在Employees表中，查询每个部门工资第二高的员工姓名、部门和工资。

建表语句： CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10, 2) );

测试数据： INSERT INTO Employees (id, name, department, salary) VALUES (1, 'Alice', 'HR', 5000), (2, 'Bob', 'HR', 6000), (3, 'Charlie', 'IT', 7000), (4, 'David', 'IT', 9000), (5, 'Eve', 'IT', 8000), (6, 'Frank', 'Sales', 4000), (7, 'Grace', 'Sales', 3000), (8, 'Heidi', 'HR', 7000);

参考答案：SELECT name, department, salary FROM ( SELECT name, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM Employees ) AS ranked WHERE rank = 2;

12.计算每个客户的首次和最后一次订单之间的天数

题目：

在Orders表中，计算每个客户的首次订单和最后一次订单之间相隔的天数。

建表语句： CREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, amount DECIMAL(10, 2) );

测试数据：INSERT INTO Orders (order_id, customer_id, order_date, amount) VALUES (1, 101, '2023-01-10', 500), (2, 101, '2023-02-15', 300), (3, 102, '2023-03-20', 700), (4, 103, '2023-04-25', 1000), (5, 101, '2023-05-10', 800);

参考答案：SELECT customer_id, DATEDIFF(MAX(order_date), MIN(order_date)) AS days_between FROM Orders GROUP BY customer_id;

13.查找没有上级的员工及其下属人数

题目：

查询所有没有上级的员工以及他们直接管理的员工数量。

建表语句：CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT ); 测试数据：INSERT INTO Employees (id, name, manager_id) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eve', 2), (6, 'Frank', 3);

参考答案：SELECT e.id, e.name, COUNT(m.id) AS subordinates_count FROM Employees e LEFT JOIN Employees m ON e.id = m.manager_id WHERE e.manager_id IS NULL GROUP BY e.id, e.name;

14.查询每个部门的平均工资，并显示与全公司平均工资的差异

题目：

查询每个部门的平均工资以及其与公司整体平均工资的差异。

建表语句：CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10, 2) );

测试数据：INSERT INTO Employees (id, name, department, salary) VALUES (1, 'Alice', 'HR', 5000), (2, 'Bob', 'HR', 6000), (3, 'Charlie', 'IT', 7000), (4, 'David', 'IT', 9000), (5, 'Eve', 'IT', 8000), (6, 'Frank', 'Sales', 4000), (7, 'Grace', 'Sales', 3000); 参考答案：

WITH AvgSalary AS ( SELECT department, AVG(salary) AS department_avg_salary FROM Employees GROUP BY department ), CompanyAvgSalary AS ( SELECT AVG(salary) AS company_avg_salary FROM Employees ) SELECT a.department, a.department_avg_salary, (a.department_avg_salary - c.company_avg_salary) AS salary_difference FROM AvgSalary a, CompanyAvgSalary c;

15.统计客户每年消费总额，并与前一年做对比

题目：

查询每个客户每年的消费总额，并计算与前一年相比的增长或减少百分比。

建表语句：CREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, amount DECIMAL(10, 2) );

测试数据：INSERT INTO Orders (order_id, customer_id, order_date, amount) VALUES (1, 101, '2022-01-15', 500), (2, 101, '2022-06-25', 600), (3, 101, '2023-02-20', 700), (4, 102, '2023-03-22', 400), (5, 102, '2022-11-12', 300);

参考答案：SELECT customer_id, YEAR(order_date) AS year, SUM(amount) AS total_amount, LAG(SUM(amount)) OVER (PARTITION BY customer_id ORDER BY YEAR(order_date)) AS previous_year_amount, (SUM(amount) - LAG(SUM(amount)) OVER (PARTITION BY customer_id ORDER BY YEAR(order_date))) / LAG(SUM(amount)) OVER (PARTITION BY customer_id ORDER BY YEAR(order_date)) * 100 AS growth_percentage FROM Orders GROUP BY customer_id, YEAR(order_date);

16.找出每个项目中工时最多的前两名员工

题目：

找出每个项目中工时最多的前两名员工的employee_id和hours。

建表语句：CREATE TABLE ProjectHours ( project_id INT, employee_id INT, hours INT );

测试数据：INSERT INTO ProjectHours (project_id, employee_id, hours) VALUES (1, 101, 50), (1, 102, 60), (1, 103, 55), (2, 101, 40), (2, 102, 45), (2, 104, 50);

参考答案：SELECT project_id, employee_id, hours FROM ( SELECT project_id, employee_id, hours, DENSE_RANK() OVER (PARTITION BY project_id ORDER BY hours DESC) AS rank FROM ProjectHours ) AS ranked WHERE rank <= 2;

17.递归查询：找出所有直属上级为某员工的下属及其子级

题目：

找出所有直属上级为指定员工的下属，及其所有间接下属。

建表语句：CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT );

测试数据：INSERT INTO Employees (id, name, manager_id) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eve', 2), (6, 'Frank', 3);

参考答案：WITH RECURSIVE Subordinates AS ( SELECT id, name, manager_id FROM Employees WHERE manager_id = :manager_id -- 替换为指定员工ID UNION ALL SELECT e.id, e.name, e.manager_id FROM Employees e JOIN Subordinates s ON e.manager_id = s.id ) SELECT * FROM Subordinates;

18.查询工资超过部门平均值的员工及其高出平均值的金额

题目：

查询每个工资高于其部门平均工资的员工及其高出平均值的金额。

建表语句：CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10, 2) );

测试数据：INSERT INTO Employees (id, name, department, salary) VALUES (1, 'Alice', 'HR', 5000), (2, 'Bob', 'HR', 6000), (3, 'Charlie', 'IT', 7000), (4, 'David', 'IT', 9000), (5, 'Eve', 'IT', 8000), (6, 'Frank', 'Sales', 4000), (7, 'Grace', 'Sales', 3000);

参考答案：WITH DepartmentAvg AS ( SELECT department, AVG(salary) AS avg_salary FROM Employees GROUP BY department ) SELECT e.name, e.salary, e.department, (e.salary - d.avg_salary) AS salary_above_avg FROM Employees e JOIN DepartmentAvg d ON e.department = d.department WHERE e.salary > d.avg_salary;

19.递归查询：计算某节点下所有子节点的总数

题目：

查询指定分类下的所有子分类总数（包括间接子分类）。

建表语句：CREATE TABLE Categories ( id INT PRIMARY KEY, category_name VARCHAR(50), parent_id INT );

测试数据：INSERT INTO Categories (id, category_name, parent_id) VALUES (1, 'Electronics', NULL), (2, 'Computers', 1), (3, 'Laptops', 2), (4, 'Smartphones', 1), (5, 'Accessories', 3);

参考答案：WITH RECURSIVE CategoryHierarchy AS ( SELECT id, parent_id FROM Categories WHERE id = :category_id -- 替换为指定分类ID UNION ALL SELECT c.id, c.parent_id FROM Categories c JOIN CategoryHierarchy ch ON c.parent_id = ch.id ) SELECT COUNT(*) AS total_subcategories FROM CategoryHierarchy;

20.查找有多个最高薪员工的部门

题目：

查询工资最高的员工有不止一位的部门。

建表语句：CREATE TABLE Employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10, 2) );

测试数据：INSERT INTO Employees (id, name, department, salary) VALUES (1, 'Alice', 'HR', 5000), (2, 'Bob', 'HR', 5000), (3, 'Charlie', 'IT', 7000), (4, 'David', 'IT', 7000), (5, 'Eve', 'Sales', 4000), (6, 'Frank', 'Sales', 3000);

参考答案：WITH MaxSalaryPerDepartment AS ( SELECT department, MAX(salary) AS max_salary FROM Employees GROUP BY department ) SELECT department FROM Employees e JOIN MaxSalaryPerDepartment m ON e.department = m.department AND e.salary = m.max_salary GROUP BY e.department HAVING COUNT(*) > 1;