欢迎来到我们SQL子查询系列的第2部分，我们将深入探讨利用子查询从HR数据库中获得强大洞察。

在本博客中，我们专注于一个实用的员工管理系统，包含员工和部门表。我们将介绍模式、插入示例数据，并演示42个高级SQL查询，强调子查询来解决复杂的HR和业务场景。本指南非常适合数据分析师、HR专业人员或开发者，他们希望通过真实世界的示例来提高SQL技能。

数据库架构概览

tbl_emp：存储员工详细信息

列：

o emp_id

o emp_name

o street_address

o city

o salary

o commission

o job

o deptno

o hiredate

o company_name

tbl_department：包含部门信息

列：

o deptno

o dept_name

创建表

CREATE TABLE tbl_department (
    deptno INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE tbl_emp (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    street_address VARCHAR(100),
    city VARCHAR(50),
    salary DECIMAL(10,2),
    commission DECIMAL(10,2),
    job VARCHAR(50),
    deptno INT,
    hiredate DATE,
    company_name VARCHAR(100),
    FOREIGN KEY (deptno) REFERENCES tbl_department(deptno)
);

插入示例数据

INSERT INTO tbl_department (deptno, dept_name) VALUES 
(10, 'Sales'), 
(20, 'HR'), 
(30, 'IT'), 
(40, 'Finance'), 
(50, 'Marketing');

INSERT INTO tbl_emp (emp_id, emp_name, street_address, city, salary, commission, job, deptno, hiredate, company_name) VALUES 
(1, 'Amit Sharma', '123 MG Road', 'Delhi', 12000.00, 500.00, 'Salesman', 10, '2015-06-10', 'First Bank Corporation'),
(2, 'Priya Verma', '456 Park Avenue', 'Mumbai', 25000.00, 2000.00, 'Manager', 20, '2012-08-15', 'First Bank Corporation'),
(3, 'Rajesh Kumar', '789 Green Street', 'Bangalore', 18000.00, 1500.00, 'Salesman', 30, '2016-02-20', 'Second Bank Corporation'),
(4, 'Anjali Gupta', '321 Ocean Drive', 'Chennai', 9500.00, 0.00, 'Clerk', 10, '2018-07-10', 'First Bank Corporation'),
(5, 'Vikram Singh', '654 Palm Street', 'Delhi', 22000.00, 3000.00, 'Salesman', 10, '2014-04-01', 'First Bank Corporation'),
(6, 'Rina Patel', '987 Sunset Boulevard', 'Ahmedabad', 13000.00, 0.00, 'Clerk', 20, '2017-05-23', 'First Bank Corporation'),
(7, 'Manoj Desai', '852 Elm Street', 'Mumbai', 30000.00, 3500.00, 'Manager', 30, '2010-01-12', 'Second Bank Corporation'),
(8, 'Sonia Reddy', '741 Maple Lane', 'Hyderabad', 15000.00, 1000.00, 'Salesman', 30, '2019-11-02', 'First Bank Corporation'),
(9, 'Sandeep Jain', '258 High Street', 'Kolkata', 20000.00, 2500.00, 'Manager', 40, '2011-03-22', 'First Bank Corporation'),
(10, 'Neha Kapoor', '963 River Road', 'Pune', 27000.00, 1500.00, 'Clerk', 50, '2013-09-14', 'Second Bank Corporation');

高级SQL子查询练习

1. First Bank Corporation中收入超过10,000美元的员工

SELECT * FROM tbl_emp
WHERE company_name = 'First Bank Corporation' AND salary > 10000;

2. 选择部门30中的员工

SELECT * FROM tbl_emp
WHERE deptno = 30;

3. 列出所有文员的姓名、编号和部门

SELECT emp_name, emp_id, deptno FROM tbl_emp
WHERE job = 'Clerk';

4. 部门编号大于20的员工的部门编号和名称

SELECT DISTINCT d.deptno, d.dept_name
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE d.deptno > 20;

5. 佣金大于工资的员工

SELECT * FROM tbl_emp
WHERE commission > salary;

6. 佣金大于工资60%的员工

SELECT * FROM tbl_emp
WHERE commission > salary * 0.6;

7. 列出部门20中收入超过2000的所有员工的姓名、工作和工资

SELECT emp_name, job, salary FROM tbl_emp
WHERE deptno = 20 AND salary > 2000;

8. 部门30中工资超过1,500美元的销售员

SELECT * FROM tbl_emp
WHERE deptno = 30 AND job = 'Salesman' AND salary > 1500;

9. 经理或总裁的员工

SELECT * FROM tbl_emp
WHERE job='Manager' or job='President';

10. 不在部门30的经理

SELECT * FROM tbl_emp
WHERE job = 'Manager' AND deptno != 30;

11. 部门10中的经理和文员

SELECT * FROM tbl_emp
WHERE deptno = 10 AND job IN ('Manager', 'Clerk');

12. 经理（任何部门）和部门20中的文员

SELECT * FROM tbl_emp
WHERE job = 'Manager' OR (job = 'Clerk' AND deptno = 20);

13. 查找部门10中所有经理的详细信息、部门20中所有文员的详细信息，以及既不是经理也不是文员但工资大于等于2000的所有员工

SELECT * FROM tbl_emp
WHERE (job = 'Manager' AND deptno = 10)
   OR (job = 'Clerk' AND deptno = 20)
   OR (job NOT IN ('Manager', 'Clerk') AND salary >= 2000);

14. 查找部门20中既不是经理也不是文员的任何人姓名

SELECT emp_name FROM tbl_emp
WHERE deptno = 20 AND job NOT IN ('Manager', 'Clerk');

15. 收入在1200到1400之间的员工

SELECT * FROM tbl_emp
WHERE salary BETWEEN 1200 AND 1400;

16. 查找是文员、分析师或销售员的员工

SELECT * FROM tbl_emp
WHERE job IN ('Clerk', 'Analyst', 'Salesman');

17. 查找不是文员、分析师或销售员的员工

SELECT * FROM tbl_emp
WHERE job NOT IN ('Clerk', 'Analyst', 'Salesman');

18. 查找工资大于2000且佣金大于200的员工

SELECT * FROM tbl_emp
WHERE salary > 2000 AND commission > 200;

19. 查找工资大于2000或佣金大于200的员工

SELECT * FROM tbl_emp
WHERE salary > 2000 OR commission > 200;

20. 查找工资大于2000且佣金大于200的员工，或者工资大于2000且佣金为0的员工

SELECT * FROM tbl_emp
WHERE (salary > 2000 AND commission > 200)
   OR (salary > 2000 AND commission = 0);

21. 查找姓名以'A'开头的员工

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE 'A%';

22. 查找姓名以'A'结尾的员工

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE '%A';

23. 查找姓名以'M'开头或结尾的所有员工

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE 'M%' OR emp_name LIKE '%M';

24. 查找姓名中包含字母'M'的所有员工（不区分大小写）

SELECT emp_name
FROM tbl_emp
WHERE emp_name LIKE '%M%' OR emp_name LIKE '%m%';

25. 查找姓名最多15个字符且姓名第3个字符为'R'的所有员工

SELECT emp_name
FROM tbl_emp
WHERE LENGTH(emp_name) <= 15 AND emp_name LIKE '__R%';

26. 查找在2月份（任何年份）被雇佣的所有员工

SELECT emp_name
FROM tbl_emp
WHERE MONTH(hiredate) = 2;

27. 查找在月末被雇佣的所有员工

SELECT emp_name
FROM tbl_emp
WHERE LAST_DAY(hiredate) = hiredate;

28. 查找2年多前被雇佣的所有员工

SELECT emp_name
FROM tbl_emp
WHERE hiredate < CURDATE() - INTERVAL 2 YEAR;

29. 查找2003年被雇佣的经理

SELECT emp_name
FROM tbl_emp
WHERE job = 'Manager' AND YEAR(hiredate) = 2003;

30. 显示所有员工的姓名和工作，用空格分隔

SELECT CONCAT(emp_name, ' ', job) AS name_and_job
FROM tbl_emp;

31. 显示所有员工的姓名，右对齐到15个字符

SELECT LPAD(emp_name, 15, ' ') FROM tbl_emp;

32. 显示所有员工的姓名，用'*'右填充到15个字符

SELECT RPAD(emp_name, 15, '*') FROM tbl_emp;

33. 显示所有员工的姓名，去掉前导'A'

SELECT TRIM(LEADING 'A' FROM emp_name) FROM tbl_emp;

34. 显示所有员工的姓名，去掉尾随'R'

SELECT TRIM(TRAILING 'R' FROM emp_name) FROM tbl_emp;

35. 显示所有员工姓名的前3个和后3个字符

SELECT CONCAT(LEFT(emp_name, 3), RIGHT(emp_name, 3)) AS first_last_chars
FROM tbl_emp;

36. 显示所有员工的姓名，将'A'替换为'a'

SELECT REPLACE(emp_name, 'A', 'a') FROM tbl_emp;

37. 显示所有员工的姓名和姓名中字符串'AR'出现的位置

SELECT emp_name, POSITION('AR' in emp_name) AS position
FROM tbl_emp;

38. 显示所有员工的工资，四舍五入到最接近的1000卢比

SELECT emp_name, ROUND(salary, -3) AS rounded_salary
FROM tbl_emp;

39. 显示员工的姓名、工作和工资，按工作和工资排序

SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job, salary;

40. 显示员工的姓名、工作和工资，按工作降序排序，在工作内按工资排序

SELECT emp_name, job, salary
FROM tbl_emp
ORDER BY job DESC, salary ASC;

41. 列出已完成1年服务的员工的姓名、部门名称和工资

SELECT e.emp_name, d.dept_name, e.salary
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE e.hiredate <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

42. 列出2003年加入的员工的姓名、部门名称和雇佣日期。按加入日期排序输出

SELECT e.emp_name, d.dept_name, e.hiredate
FROM tbl_emp e
JOIN tbl_department d ON e.deptno = d.deptno
WHERE YEAR(e.hiredate) = 2003
ORDER BY e.hiredate;

结论

我们SQL子查询系列的第2部分展示了高级查询来提取关键的HR洞察，重点关注用于复杂过滤和比较的子查询。

从分析员工收入到跟踪雇佣日期和姓名模式，这些查询展示了SQL在HR数据管理中的强大功能。

使用这些示例作为基础来构建你自己的查询并推动明智的业务决策。