在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;
在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;
查询所有没有上级的员工以及他们直接管理的员工数量。
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;
查询每个部门的平均工资以及其与公司整体平均工资的差异。
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;
查询每个客户每年的消费总额,并计算与前一年相比的增长或减少百分比。
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);
找出每个项目中工时最多的前两名员工的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;
找出所有直属上级为指定员工的下属,及其所有间接下属。
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;
查询每个工资高于其部门平均工资的员工及其高出平均值的金额。
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;
查询指定分类下的所有子分类总数(包括间接子分类)。
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;
查询工资最高的员工有不止一位的部门。
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;