当前位置：首页>python>SQl实现:最底薪的员工与python3单元测试

SQl实现:最底薪的员工与python3单元测试

  • 2026-09-02 20:25:07
SQl实现:最底薪的员工与python3单元测试

问题描述

在 Employees表中：

  • employee_id是主键

  • manager_id表示上级经理的 ID（可为空）

  • 当经理离职时，他们从表中被删除，但员工记录中的 manager_id仍保留原值

任务：查找满足以下条件的员工：

  1. 薪水严格少于 $30,000

  2. 上级经理已离职（即 manager_id在表中不存在）

返回结果按 employee_id升序排序

示例数据

输出：

employee_id

11

解释：

  • Kalel (ID=1) 薪水 21,241<30,000，但经理 ID=11 (Joziah) 仍在表中

  • Joziah (ID=11) 薪水 28,485<30,000，经理 ID=6 不在表中（已离职）

SQL 解决方案

方法一：使用 LEFT JOIN（推荐）

SELECT e.employee_id FROM Employees e LEFT JOIN Employees m ON e.manager_id = m.employee_id  WHERE e.salary < 30000    AND e.manager_id IS NOT NULL    AND m.employee_id IS NULL ORDER BY e.employee_id;

方法二：使用子查询

SELECT employee_id FROM Employees WHERE salary < 30000    AND manager_id IS NOT NULL     AND manager_id NOT IN (SELECT employee_id FROM Employees)    ORDER BY employee_id;

方法三：使用 NOT EXISTS

SELECT e.employee_id FROM Employees e WHERE e.salary < 30000   AND e.manager_id IS NOT NULL   AND NOT EXISTS (     SELECT 1      FROM Employees m      WHERE m.employee_id = e.manager_id   )  ORDER BY e.employee_id;

关键点解析

  1. 核心条件：

  • salary < 30000：筛选低薪员工

  • manager_id IS NOT NULL：排除无经理的员工

  • 经理不存在于表中：LEFT JOIN后 m.employee_id IS NULL或 NOT IN/NOT EXISTS

  • 连接逻辑：

  • 自连接表：Employees e（员工）和 Employees m（经理）

  • 连接条件：e.manager_id = m.employee_id

  • 无效经理：m.employee_id IS NULL

  • 排序要求：

  • ORDER BY employee_id：升序排列结果

示例验证

-- 创建测试表 CREATE TABLE Employees (     employee_id INT PRIMARY KEY,     name VARCHAR(50),       manager_id INT,     salary INT );    -- 插入示例数据 INSERT INTO Employees VALUES (3,   'Mila', 9, 60301), (12, 'Antonella', NULL, 31000),    (13, 'Emery', NULL, 67084), (1, 'Kalel', 11, 21241),    (9, 'Mikaela', NULL, 50937), (11, 'Joziah', 6, 28485);     -- 执行查询（方法一）     SELECT e.employee_id FROM Employees e LEFT JOIN     Employees m ON e.manager_id = m.employee_id     WHERE e.salary < 30000       AND e.manager_id IS NOT NULL        AND m.employee_id IS NULL ORDER BY e.employee_id;

结果：

employee_id

11

性能优化建议

  1. 在大型数据集上，NOT EXISTS通常性能最佳

  2. 为 manager_id和 salary添加索引：

  1. CREATE INDEX idx_manager ON Employees(manager_id); CREATE INDEX idx_salary ON Employees(salary);

  2. 避免使用 NOT IN（当子查询可能返回 NULL 时有风险）

扩展思考

  1. 如何同时返回员工姓名和经理状态？

  2. 如何统计各部门无有效经理的低薪员工数量？

  3. 如何处理多级经理关系（经理解雇后，新经理未分配）？

提示：这类问题常见于组织架构分析、权限管理和数据完整性检查场景。掌握自连接和条件过滤是解决此类问题的关键！

单元测试：

以下是一个完整的Python单元测试脚本，用于测试您提供的SQL查询。该脚本使用SQLite内存数据库模拟数据环境，并验证查询结果的正确性：

import sqlite3import unittestfrom contextlib import contextmanager@contextmanagerdef create_in_memory_db():    """创建内存数据库上下文管理器"""    conn = sqlite3.connect(':memory:')    try:        yield conn    finally:        conn.close()class TestEmployeesWithoutManager(unittest.TestCase):    def setUp(self):        """创建内存数据库并初始化表结构"""        self.conn = sqlite3.connect(':memory:')        self.cursor = self.conn.cursor()        # 创建Employees表        self.cursor.execute('''            CREATE TABLE Employees (                employee_id INTEGER PRIMARY KEY,                name TEXT NOT NULL,                manager_id INTEGER,                salary INTEGER NOT NULL            )        ''')        self.conn.commit()    def tearDown(self):        """关闭数据库连接"""        self.conn.close()    def execute_query(self):        """执行目标SQL查询并返回结果"""        query = """        SELECT e.employee_id        FROM Employees e        LEFT JOIN Employees m ON e.manager_id = m.employee_id        WHERE e.salary < 30000          AND e.manager_id IS NOT NULL          AND m.employee_id IS NULL        ORDER BY e.employee_id;        """        self.cursor.execute(query)        return [row[0] for row in self.cursor.fetchall()]  # 返回employee_id列表    def load_data(self, data):        """向Employees表加载测试数据"""        self.cursor.executemany(            "INSERT INTO Employees (employee_id, name, manager_id, salary) VALUES (?, ?, ?, ?)",            data        )        self.conn.commit()    def test_example_case(self):        """测试示例数据"""        # 示例数据        data = [            (3, 'Mila', 9, 60301),            (12, 'Antonella', None, 31000),            (13, 'Emery', None, 67084),            (1, 'Kalel', 11, 21241),            (9, 'Mikaela', None, 50937),            (11, 'Joziah', 6, 28485)        ]        self.load_data(data)        # 执行查询        results = self.execute_query()        # 验证结果        self.assertEqual(results, [11])  # 应输出employee_id=11    def test_no_employees_meet_criteria(self):        """测试没有员工满足条件的情况"""        data = [            (1, 'Alice', 2, 35000),  # 薪水过高            (2, 'Bob', None, 28000),  # 无经理            (3, 'Charlie', 4, 29000), # 经理存在            (4, 'David', 5, 25000)   # 经理存在        ]        self.load_data(data)        results = self.execute_query()        self.assertEqual(results, [])  # 应返回空列表    def test_multiple_employees_meet_criteria(self):        """测试多个员工满足条件的情况"""        data = [            (1, 'Alice', 10, 25000),  # 经理不存在            (2, 'Bob', 20, 28000),    # 经理不存在            (3, 'Charlie', 30, 29000), # 经理不存在            (4, 'David', 1, 22000),    # 经理存在            (5, 'Eve', None, 27000)    # 无经理        ]        self.load_data(data)        results = self.execute_query()        self.assertEqual(sorted(results), [1, 2, 3])  # 应输出1,2,3（按升序）    def test_employee_with_null_manager(self):        """测试经理ID为NULL的员工"""        data = [            (1, 'Alice', None, 25000),  # 无经理            (2, 'Bob', 3, 28000),        # 经理存在            (3, 'Charlie', 4, 29000),    # 经理存在            (4, 'David', None, 20000)    # 无经理        ]        self.load_data(data)        results = self.execute_query()        self.assertEqual(results, [])  # 应返回空列表    def test_employee_with_existing_manager(self):        """测试有有效经理的员工"""        data = [            (1, 'Alice', 2, 25000),  # 经理存在            (2, 'Bob', 3, 28000),     # 经理存在            (3, 'Charlie', 1, 29000)  # 经理存在        ]        self.load_data(data)        results = self.execute_query()        self.assertEqual(results, [])  # 应返回空列表    def test_employee_with_non_existent_manager(self):        """测试经理不存在的员工"""        data = [            (1, 'Alice', 100, 25000),  # 经理不存在            (2, 'Bob', 200, 28000),     # 经理不存在            (3, 'Charlie', 300, 29000)  # 经理不存在        ]        self.load_data(data)        results = self.execute_query()        self.assertEqual(sorted(results), [1, 2, 3])  # 应输出1,2,3    def test_salary_boundary_conditions(self):        """测试薪水边界条件"""        data = [            (1, 'Alice', 10, 29999),  # 刚好低于30000            (2, 'Bob', 20, 30000),    # 等于30000（不满足）            (3, 'Charlie', 30, 30001) # 高于30000        ]        self.load_data(data)        results = self.execute_query()        self.assertEqual(results, [1])  # 应输出1    def test_manager_is_self(self):        """测试经理是自己的情况（循环依赖）"""        data = [            (1, 'Alice', 1, 25000)  # 经理是自己        ]        self.load_data(data)        results = self.execute_query()        self.assertEqual(results, [])  # 应返回空（因为经理存在）    def test_complex_hierarchy(self):        """测试复杂层级关系"""        data = [            (1, 'CEO', None, 100000),            (2, 'Manager', 1, 60000),            (3, 'Employee1', 2, 25000),  # 有效经理            (4, 'Employee2', 5, 28000),  # 经理不存在            (5, 'Ex-Manager', None, 55000),  # 经理已离职            (6, 'Employee3', 7, 29000),  # 经理不存在            (7, 'Ex-Manager2', 8, 52000), # 经理存在            (8, 'Ex-Manager3', None, 51000) # 经理已离职        ]        self.load_data(data)        results = self.execute_query()        # 应返回4和6（Employee2和Employee3）        self.assertEqual(sorted(results), [4, 6])    def test_empty_table(self):        """测试空表情况"""        results = self.execute_query()        self.assertEqual(results, [])  # 应返回空列表    def test_negative_salary(self):        """测试负薪水情况"""        data = [            (1, 'Alice', 10, -5000),  # 负薪水            (2, 'Bob', 20, 28000)      # 正薪水        ]        self.load_data(data)        results = self.execute_query()        # 负薪水也满足<30000，且经理不存在        self.assertEqual(results, [1])if __name__ == '__main__':    unittest.main(verbosity=2)

测试脚本说明：

  1. 测试环境设置：

    • 使用SQLite内存数据库模拟真实环境

    • 创建与问题描述一致的Employees表结构

    • 每个测试后自动清理数据库

  2. 核心测试方法：

    • execute_query(): 执行目标SQL查询并返回结果

    • load_data(): 向表中加载测试数据

    • 多个测试用例覆盖不同场景

  3. 测试用例覆盖：

    • 示例场景：验证题目给出的标准示例

    • 无满足条件员工：所有员工都不符合要求

    • 多个满足条件员工：多个员工都符合"无有效经理且低薪"

    • 经理为NULL：测试无经理的员工

    • 有有效经理：测试经理仍在职的员工

    • 经理不存在：测试经理已离职的员工

    • 薪水边界：测试29999、30000、30001等边界值

    • 自引用经理：测试经理是自己的情况

    • 复杂层级：测试多层管理关系

    • 空表处理：数据库为空时的边界情况

    • 负薪水：测试负收入情况

  4. 断言机制：

    • 验证返回的employee_id列表是否符合预期

    • 使用精确的整数匹配

    • 处理可能的异常情况

使用说明：

  1. 将脚本保存为test_employees.py

  2. 运行命令：python test_employees.py

  3. 查看测试结果：

    • 所有测试通过会显示OK

    • 失败测试会显示具体差异

关键测试点说明：

  1. 核心逻辑验证：

    • 验证salary < 30000条件

    • 验证manager_id IS NOT NULL条件

    • 验证经理不存在的逻辑（LEFT JOIN后m.employee_id IS NULL）

  2. 边界条件处理：

    • 薪水正好为30000（不应包含）

    • 薪水为负值（应包含）

    • 经理ID为NULL（应排除）

    • 经理ID指向不存在的员工（应包含）

  3. 排序要求：

    • 结果按employee_id升序排列

    • 使用sorted()处理可能乱序的结果

  4. 特殊场景：

    • 循环依赖（经理是自己）

    • 多层管理关系

    • 混合情况（部分员工满足，部分不满足）

这个测试脚本全面覆盖了SQL查询的各种边界情况和业务逻辑，确保您的查询在各种场景下都能正确工作。特别针对员工-经理关系的复杂性，设计了多种层级结构和异常情况的测试用例，验证了算法在复杂组织关系下的正确性。

本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：596563534@qq.com 。

每天学习一点Python——SQLite数据库操作入门

Linux LTS 时间线重写:这次调整可能影响整个行业节奏

最新文章

随机文章