探客时代

数据库系列（5部分）

📝 面向新手的DBMS面试题

📝 面向新手的10大SQL查询

掌握SQL基础：（第2部分）

掌握SQL基础：（第1部分）

掌握SQL连接查询：HR员工数据分析

欢迎来到我们的SQL连接查询系列，我们将深入探讨使用连接操作分析HR数据的高级技术。本博客专注于一个员工管理数据库，包含Worker、Department和Worker_rating表。我们将提供45个SQL查询，利用各种连接类型（INNER、LEFT等）来处理复杂的HR场景。这些查询非常适合数据分析师、HR专业人员或开发者，他们希望掌握SQL连接以获得员工洞察。

🧩 数据库架构概览

Worker：存储员工详细信息

列：

worker_id

first_name

last_name

salary

joining_date

department_id

Department：包含部门信息

列：

id

department

Worker_rating：跟踪员工绩效评级

列：

id

worker_id

rating

🏗️ 创建表

CREATE TABLE Department (
id INT PRIMARY KEY AUTO_INCREMENT,
department VARCHAR(50) NOT NULL
);
CREATE TABLE Worker (
worker_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
salary INT NOT NULL,
joining_date DATE NOT NULL,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Department(id)
);
CREATE TABLE Worker_rating (
id INT PRIMARY KEY AUTO_INCREMENT,
worker_id INT,
rating INT NOT NULL,
FOREIGN KEY (worker_id) REFERENCES Worker(worker_id)
);

📝 插入示例数据

INSERT INTO Department (id, department) VALUES
(1, 'HR'),
(2, 'Admin'),
(3, 'Account'),
(4, 'Sales');
INSERT INTO Worker (worker_id, first_name, last_name, salary, joining_date, department_id) VALUES
(1, 'Monika', 'Arora', 100000, '1982-02-20', 1),
(2, 'Niharika', 'Verma', 80000, '2014-06-11', 2),
(3, 'Vishal', 'Singhal', 300000, '2015-02-20', 1),
(4, 'Amitabh', 'Singh', 500000, '2014-02-20', 2),
(5, 'Vivek', 'Bhati', 500000, '2016-06-11', 2),
(6, 'Vipul', 'Diwan', 200000, '2014-06-11', 3),
(7, 'Satish', 'Kumar', 75000, '1985-01-20', 4),
(8, 'Geetika', 'Chauhan', 90000, '1988-04-11', 2),
(9, 'Ramesh', 'Sharma', 120000, '2018-03-15', 1),
(10, 'Suresh', 'Yadav', 95000, '2017-07-01', 3),
(11, 'Anjali', 'Kapoor', 85000, '2013-01-25', 4),
(12, 'Pooja', 'Malhotra', 110000, '2016-12-10', 2),
(13, 'Deepak', 'Gupta', 140000, '2019-09-05', 1),
(14, 'Rohit', 'Jain', 70000, '2020-01-20', 3),
(15, 'Sneha', 'Mehra', 80000, '2012-06-25', 4),
(16, 'Arjun', 'Chopra', 130000, '2021-03-14', 1),
(17, 'Kiran', 'Reddy', 120000, '2022-05-18', 2),
(18, 'Meena', 'Naik', 110000, '2020-08-21', 1),
(19, 'Rajesh', 'Pillai', 95000, '2018-11-30', 4),
(20, 'Bhavna', 'Iyer', 105000, '2015-07-22', 3),
(21, 'Prakash', 'Joshi', 98000, '2016-04-25', 2),
(22, 'Gaurav', 'Patel', 88000, '2019-10-15', 3),
(23, 'Manisha', 'Desai', 102000, '2014-11-20', 2),
(24, 'Ajay', 'Khan', 92000, '2021-01-12', 4),
(25, 'Sanjay', 'Ghosh', 135000, '2017-03-08', 1),
(26, 'Priya', 'Das', 80000, '2018-05-14', 2),
(27, 'Anil', 'Nair', 125000, '2020-09-16', 3),
(28, 'Siddharth', 'Rao', 140000, '2022-11-10', 4),
(29, 'Radha', 'Kulkarni', 95000, '2015-12-23', 2),
(30, 'Tanya', 'Mishra', 89000, '2019-06-29', 3);
INSERT INTO Worker_rating (id, worker_id, rating) VALUES
(1, 1, 3),
(2, 1, 2),
(3, 2, 4),
(4, 3, 5),
(5, 4, 2),
(6, 2, 1),
(7, 3, 3),
(8, 5, 2),
(9, 6, 5),
(10, 7, 4),
(11, 8, 3),
(12, 9, 4),
(13, 10, 3),
(14, 11, 4),
(15, 12, 2),
(16, 13, 5),
(17, 14, 3),
(18, 15, 4),
(19, 16, 5),
(20, 17, 2),
(21, 18, 3),
(22, 19, 4),
(23, 20, 5),
(24, 21, 2),
(25, 22, 4),
(26, 23, 3),
(27, 24, 2),
(28, 25, 5),
(29, 26, 3),
(30, 27, 4);

🔍 员工分析的高级SQL连接查询

以下是45个SQL查询及其解释，利用连接操作来组合Worker、Department和Worker_rating表的数据，以获得可操作的HR洞察。

1. 检索所有员工的名字和姓氏以及他们的部门名称

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id;

2. 检索每个部门员工的总工资

SELECT d.department, SUM(w.salary) AS total_salary
FROM Department d
JOIN Worker w ON d.id = w.department_id
GROUP BY d.department;

3. 获取按部门分组的员工数量

SELECT d.department, COUNT(w.worker_id) AS number_of_workers
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING AVG(w.salary) > 100000;

4. 获取每个部门的最高工资记录

SELECT d.department, MAX(w.salary) AS highest_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

5. 获取评级大于等于4的员工的平均评级和详细信息

SELECT w.first_name, w.last_name, w.salary, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 4
GROUP BY w.worker_id, w.first_name, w.last_name, w.salary;

6. 获取评级为3或以上的员工列表，包括部门名称和工资

SELECT w.first_name, w.last_name, d.department, w.salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 3;

7. 显示评级为4或以上的员工按部门分组的平均工资

SELECT d.department, AVG(w.salary) AS average_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating >= 4
GROUP BY d.department;

8. 查找每个部门中工资最高的员工

SELECT w.first_name, w.last_name, d.department, w.salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary = (
SELECT MAX(salary)
FROM Worker w2
WHERE w2.department_id = w.department_id
);

9. 获取每个部门的员工数量和平均工资

SELECT d.department, COUNT(w.worker_id) AS worker_count, AVG(w.salary) AS avg_salary
FROM Department d
LEFT JOIN Worker w ON d.id = w.department_id
GROUP BY d.department;

10. 查找评级为5的员工及其部门信息

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 5;

11. 获取每个部门的最高和最低工资

SELECT d.department, MAX(w.salary) AS max_salary, MIN(w.salary) AS min_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

12. 查找平均评级最高的部门

SELECT d.department, AVG(r.rating) AS avg_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
ORDER BY avg_rating DESC
LIMIT 1;

13. 获取工资高于部门平均工资的员工

SELECT w.first_name, w.last_name, w.salary, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary > (
SELECT AVG(salary)
FROM Worker w2
WHERE w2.department_id = w.department_id
);

14. 查找每个部门中评级最高的员工

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = (
SELECT MAX(rating)
FROM Worker_rating r2
JOIN Worker w2 ON r2.worker_id = w2.worker_id
WHERE w2.department_id = w.department_id
);

15. 获取每个部门的工资分布（高、中、低）

SELECT d.department,
COUNT(CASE WHEN w.salary >= 100000 THEN 1 END) AS high_salary,
COUNT(CASE WHEN w.salary BETWEEN 50000 AND 99999 THEN 1 END) AS medium_salary,
COUNT(CASE WHEN w.salary < 50000 THEN 1 END) AS low_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

16. 查找评级低于3的员工及其部门

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating < 3;

17. 获取每个部门的工资总和和员工数量

SELECT d.department, SUM(w.salary) AS total_salary, COUNT(w.worker_id) AS worker_count
FROM Department d
LEFT JOIN Worker w ON d.id = w.department_id
GROUP BY d.department;

18. 查找评级为1的员工及其部门信息

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 1;

19. 获取每个部门的平均评级

SELECT d.department, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department;

20. 查找工资最高的前5名员工及其部门

SELECT w.first_name, w.last_name, d.department, w.salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
ORDER BY w.salary DESC
LIMIT 5;

21. 获取评级为4或5的员工数量按部门分组

SELECT d.department, COUNT(w.worker_id) AS high_rated_workers
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating IN (4, 5)
GROUP BY d.department;

22. 查找每个部门中评级最低的员工

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = (
SELECT MIN(rating)
FROM Worker_rating r2
JOIN Worker w2 ON r2.worker_id = w2.worker_id
WHERE w2.department_id = w.department_id
);

23. 获取每个部门的工资范围

SELECT d.department,
MAX(w.salary) - MIN(w.salary) AS salary_range,
MAX(w.salary) AS max_salary,
MIN(w.salary) AS min_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

24. 查找评级为2的员工及其部门信息

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 2;

25. 获取每个部门的平均工资和平均评级

SELECT d.department, AVG(w.salary) AS avg_salary, AVG(r.rating) AS avg_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department;

26. 查找评级为3的员工及其部门信息

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 3;

27. 获取每个部门的工资中位数

SELECT d.department,
AVG(w.salary) AS median_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
WHERE w.salary IN (
SELECT w2.salary
FROM Worker w2
WHERE w2.department_id = w.department_id
ORDER BY w2.salary
LIMIT 1 OFFSET (
SELECT COUNT(*) / 2
FROM Worker w3
WHERE w3.department_id = w.department_id
)
)
GROUP BY d.department;

28. 查找评级为4的员工及其部门信息

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 4;

29. 获取每个部门的工资标准差

SELECT d.department,
STDDEV(w.salary) AS salary_stddev
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

30. 查找评级为5的员工及其部门信息

SELECT w.first_name, w.last_name, d.department, r.rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 5;

31. 获取每个部门的工资分布百分比

SELECT d.department,
ROUND(COUNT(CASE WHEN w.salary >= 100000 THEN 1 END) * 100.0 / COUNT(*), 2) AS high_salary_percent,
ROUND(COUNT(CASE WHEN w.salary BETWEEN 50000 AND 99999 THEN 1 END) * 100.0 / COUNT(*), 2) AS medium_salary_percent,
ROUND(COUNT(CASE WHEN w.salary < 50000 THEN 1 END) * 100.0 / COUNT(*), 2) AS low_salary_percent
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

32. 查找评级分布按部门分组

SELECT d.department,
COUNT(CASE WHEN r.rating = 1 THEN 1 END) AS rating_1,
COUNT(CASE WHEN r.rating = 2 THEN 1 END) AS rating_2,
COUNT(CASE WHEN r.rating = 3 THEN 1 END) AS rating_3,
COUNT(CASE WHEN r.rating = 4 THEN 1 END) AS rating_4,
COUNT(CASE WHEN r.rating = 5 THEN 1 END) AS rating_5
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department;

33. 列出最高工资大于300,000的部门

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
HAVING MAX(w.salary) > 300000;

34. 显示员工平均评级恰好为2的部门

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) = 2;

35. 获取平均评级小于3且总工资大于1,000,000的部门

SELECT d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING AVG(r.rating) < 3 AND SUM(w.salary) > 1000000;

36. 检索评级为1或2的员工及其部门名称

SELECT w.first_name, w.last_name, d.department
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
WHERE r.rating = 1 OR r.rating = 2;

37. 查找总工资最高的部门

SELECT d.department, SUM(w.salary) AS total_salary
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department
ORDER BY total_salary DESC
LIMIT 1;

38. 查找平均评级最低的部门，排除没有评级的部门

SELECT d.department, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
ORDER BY average_rating ASC
LIMIT 1;

39. 查找总工资大于500,000的部门的总工资和平均评级

SELECT d.department, SUM(w.salary) AS total_salary, AVG(r.rating) AS average_rating
FROM Worker w
JOIN Department d ON w.department_id = d.id
JOIN Worker_rating r ON w.worker_id = r.worker_id
GROUP BY d.department
HAVING SUM(w.salary) > 500000;

40. 使用GROUP_CONCAT获取按部门分组的员工姓名

SELECT d.department, GROUP_CONCAT(CONCAT(w.first_name, ' ', w.last_name)) AS worker_names
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

41. 获取过去5年加入的员工

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE w.joining_date >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

42. 获取每年加入的员工数量

SELECT YEAR(w.joining_date) AS join_year, COUNT(w.worker_id) AS worker_count
FROM Worker w
GROUP BY YEAR(w.joining_date);

43. 获取每个部门的最早和最晚加入日期

SELECT d.department, MIN(w.joining_date) AS earliest_joining, MAX(w.joining_date) AS latest_joining
FROM Worker w
JOIN Department d ON w.department_id = d.id
GROUP BY d.department;

44. 获取在公司工作超过10年的员工

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE w.joining_date < DATE_SUB(CURDATE(), INTERVAL 10 YEAR);

45. 检索今天加入的员工

SELECT w.first_name, w.last_name, w.joining_date
FROM Worker w
WHERE DATE(w.joining_date) = CURDATE();

🎉 结论

本博客演示了45个SQL查询，利用连接操作从员工数据库中提取可操作的HR洞察。从分析工资和评级到跟踪加入日期，这些查询展示了SQL连接在员工管理中的强大功能。练习这些示例以增强你的HR数据分析能力，并通过高效的基于连接的查询推动明智的决策。

关键词：SQL、数据库管理系统、数据库、教程、连接查询、HR分析