SQL复杂查询语句使用示例

SQL 复杂查询语句通常涉及多个表的连接、子查询、聚合函数、窗口函数、条件过滤等高级功能。╮(╯▽╰)╭

1. 多表连接（JOIN）

多表连接是 SQL 中常见的复杂查询之一，用于从多个表中检索相关数据。

示例：内连接（INNER JOIN）

假设有两个表 employees 和 departments，我们想要查询每个员工及其所属的部门名称。

SELECT 
    employees.employee_id,
    employees.first_name,
    employees.last_name,
    departments.department_name
FROM 
    employees
INNER JOIN 
    departments ON employees.department_id = departments.department_id;

示例：左连接（LEFT JOIN）

假设我们想要查询所有员工及其所属的部门名称，即使某些员工没有分配到部门。

SELECT 
    employees.employee_id,
    employees.first_name,
    employees.last_name,
    departments.department_name
FROM 
    employees
LEFT JOIN 
    departments ON employees.department_id = departments.department_id;

2. 子查询（Subquery）

子查询是嵌套在另一个查询中的查询，可以用于过滤、计算等。

示例：使用子查询进行过滤

假设我们想要查询工资高于平均工资的员工。

SELECT 
    employee_id,
    first_name,
    last_name,
    salary
FROM 
    employees
WHERE 
    salary > (SELECT AVG(salary) FROM employees);

示例：使用子查询进行计算

假设我们想要查询每个部门的最高工资。

SELECT 
    department_id,
    MAX(salary) AS highest_salary
FROM 
    employees
GROUP BY 
    department_id;

3. 聚合函数与分组（Aggregation and GROUP BY）

聚合函数用于对一组值执行计算并返回单个值。通常与 GROUP BY 子句一起使用。

示例：计算每个部门的平均工资

SELECT 
    department_id,
    AVG(salary) AS average_salary
FROM 
    employees
GROUP BY 
    department_id;

示例：计算每个部门的员工数量

SELECT 
    department_id,
    COUNT(employee_id) AS employee_count
FROM 
    employees
GROUP BY 
    department_id;

4. 窗口函数（Window Functions）

窗口函数用于在查询结果集的特定窗口内执行计算，而不分组数据。

示例：计算每个部门的累计工资

SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS cumulative_salary
FROM 
    employees;

示例：计算每个员工的排名

SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM 
    employees;

5. 条件过滤与排序（WHERE 和 ORDER BY）

条件过滤和排序是 SQL 查询中常用的子句，用于筛选和组织数据。

示例：按部门和工资排序

SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary
FROM 
    employees
WHERE 
    department_id = 10
ORDER BY 
    salary DESC;

6. 复杂条件过滤（Complex WHERE Clauses）

使用 AND、OR 和 NOT 等逻辑运算符进行复杂的条件过滤。

示例：查询特定条件的员工

SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary
FROM 
    employees
WHERE 
    department_id = 10
    AND salary > 5000
    AND hire_date > '2020-01-01';

7. 使用 UNION 和 UNION ALL 合并查询结果

UNION 和 UNION ALL 用于合并两个或多个 SELECT 语句的结果集。

示例：合并两个查询结果

SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary
FROM 
    employees
WHERE 
    department_id = 10

UNION

SELECT 
    employee_id,
    first_name,
    last_name,
    department_id,
    salary
FROM 
    employees
WHERE 
    salary > 10000;

8. 使用 EXISTS 和 NOT EXISTS 进行条件过滤

EXISTS 和 NOT EXISTS 用于检查子查询是否返回任何行。

示例：查询有员工的部门

SELECT 
    department_id,
    department_name
FROM 
    departments
WHERE 
    EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id);

示例：查询没有员工的部门

SELECT 
    department_id,
    department_name
FROM 
    departments
WHERE 
    NOT EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id);

9. 使用 CASE 表达式进行条件逻辑

CASE 表达式用于在 SQL 查询中实现条件逻辑。

示例：根据工资等级分类

SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    CASE 
        WHEN salary < 5000 THEN 'Low'
        WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
        ELSE 'High'
    END AS salary_grade
FROM 
    employees;

10. 使用 CTE（Common Table Expressions）

CTE 是一种命名的临时结果集，可以简化复杂的查询。

示例：使用 CTE 计算每个部门的平均工资

WITH DepartmentAverage AS (
    SELECT 
        department_id,
        AVG(salary) AS average_salary
    FROM 
        employees
    GROUP BY 
        department_id
)
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    e.department_id,
    e.salary,
    da.average_salary
FROM 
    employees e
JOIN 
    DepartmentAverage da ON e.department_id = da.department_id;