写几个难的sql语句

以下是几个比较难的SQL语句示例：

1. 找出每个部门中平均工资最高的员工：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) = (
    SELECT MAX(avg_salary)
    FROM (
        SELECT department, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department
    ) AS subquery
)

2. 找出至少在3个不同部门工作过的员工：

SELECT employee_id, COUNT(DISTINCT department) AS num_departments
FROM employee_departments
GROUP BY employee_id
HAVING COUNT(DISTINCT department) >= 3

3. 找出至少与另一个员工在2个相同项目中工作过的员工：

SELECT e1.employee_id, e2.employee_id
FROM employees e1
JOIN employees e2 ON e1.employee_id <> e2.employee_id
JOIN projects p1 ON e1.employee_id = p1.employee_id
JOIN projects p2 ON e2.employee_id = p2.employee_id
WHERE p1.project_id = p2.project_id
GROUP BY e1.employee_id, e2.employee_id
HAVING COUNT(DISTINCT p1.project_id) >= 2

这些示例SQL语句可能会因数据库结构、表和列的名称等因素而有所不同，需要根据具体情况进行调整。