在SQLite中查询同时掌握指定编程语言的员工方法

一、先修正你原来的单语言查询SQL #

你之前写的单语言查询缺少JOIN的关联条件，正确的查询掌握C++的员工语句应该是：

SELECT e.employee_name
FROM employees e
JOIN employee_known_languages ek ON e.employee_id = ek.employee_id
JOIN programming_languages pl ON ek.language_id = pl.language_id
WHERE pl.language_name = 'C++';

二、查询同时掌握多种语言的SQL（以Python和C++为例） #

要找出同时掌握指定多种语言的员工，核心思路是先筛选出掌握目标语言之一的员工，再按员工分组，统计其匹配到的目标语言数量，确保数量等于目标语言的总数：

SELECT e.employee_name
FROM employees e
JOIN employee_known_languages ek ON e.employee_id = ek.employee_id
JOIN programming_languages pl ON ek.language_id = pl.language_id
WHERE pl.language_name IN ('Python', 'C++')
GROUP BY e.employee_id, e.employee_name
HAVING COUNT(DISTINCT pl.language_name) = 2;

这里用DISTINCT是为了避免同一员工重复关联同一种语言的情况（比如员工多次记录掌握同一种语言），保证统计的是不同语言的数量。

三、通用化SQL模板（适配任意语言子集） #

如果要查询掌握N种指定语言的员工，只需要替换IN里的语言列表，同时把HAVING后的数字改成N即可。比如要找同时掌握Python、C++、JavaScript的员工：

SELECT e.employee_name
FROM employees e
JOIN employee_known_languages ek ON e.employee_id = ek.employee_id
JOIN programming_languages pl ON ek.language_id = pl.language_id
WHERE pl.language_name IN ('Python', 'C++', 'JavaScript')
GROUP BY e.employee_id, e.employee_name
HAVING COUNT(DISTINCT pl.language_name) = 3;

四、Python动态查询函数实现 #

下面是一个可以传入任意语言列表、自动生成SQL查询的Python函数，用sqlite3为例（其他数据库如MySQL只需调整连接方式）：

import sqlite3

def get_employees_by_languages(db_path, target_languages):
    if not target_languages:
        return []
    
    # 生成SQL占位符，避免SQL注入风险
    placeholders = ', '.join(['?'] * len(target_languages))
    query = f"""
        SELECT e.employee_name
        FROM employees e
        JOIN employee_known_languages ek ON e.employee_id = ek.employee_id
        JOIN programming_languages pl ON ek.language_id = pl.language_id
        WHERE pl.language_name IN ({placeholders})
        GROUP BY e.employee_id, e.employee_name
        HAVING COUNT(DISTINCT pl.language_name) = ?
    """
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    # 传入参数：目标语言列表 + 语言总数
    cursor.execute(query, target_languages + [len(target_languages)])
    results = [row[0] for row in cursor.fetchall()]
    conn.close()
    return results

# 调用示例
if __name__ == "__main__":
    # 假设数据库文件为employees.db
    matched_employees = get_employees_by_languages('employees.db', ['Python', 'C++'])
    print("同时掌握Python和C++的员工：", matched_employees)  # 输出结果：['Bob']

内容的提问来源于stack exchange，提问作者Nathan Pierson