第5章　复杂查询-SQL基础教程(中)

当前位置:　首页>> 技术小册>> SQL基础教程(中)

第5章复杂查询

在掌握了SQL的基本操作，如数据查询（SELECT）、数据插入（INSERT）、更新（UPDATE）和删除（DELETE）之后，你将面临更复杂的数据检索需求。复杂查询是SQL学习中不可或缺的一环，它们能够帮助你从数据库中提取出经过精细筛选、排序、分组及聚合处理的数据集。本章将深入探讨SQL中的复杂查询技术，包括子查询、连接（JOINs）、窗口函数（Window Functions）以及公用表表达式（Common Table Expressions, CTEs），帮助你进一步提升数据处理和分析的能力。

5.1 子查询（Subqueries）

子查询是嵌套在其他SQL查询中的查询。它们可以作为SELECT、FROM、WHERE或HAVING子句的一部分，用于实现更复杂的条件筛选和数据处理。

5.1.1 在SELECT子句中使用子查询

子查询可以直接在SELECT列表中返回单个值或多个值，用于作为查询结果的一部分。

SELECT 
    EmployeeID, 
    Name, 
    (SELECT AVG(Salary) FROM Employees) AS AverageSalary
FROM 
    Employees;

这个例子中，子查询计算了所有员工的平均薪资，并将其作为每一行结果的额外列显示。

5.1.2 在FROM子句中使用子查询

将子查询作为临时表在FROM子句中引用，可以对子查询的结果进行进一步的处理。

SELECT 
    DeptID, 
    COUNT(*) AS NumberOfEmployees
FROM 
    (SELECT DISTINCT DeptID, EmployeeID FROM EmployeeProjects) AS DeptEmployees
GROUP BY 
    DeptID;

此查询通过子查询先找出每个部门有哪些员工，然后在外层查询中计算每个部门的员工数量。

5.1.3 在WHERE子句中使用子查询

子查询在WHERE子句中常用于设置过滤条件，根据其他表或同一表的其他记录来筛选记录。

SELECT 
    *
FROM 
    Employees
WHERE 
    Salary > (SELECT AVG(Salary) FROM Employees);

此查询选出薪资高于公司平均薪资的所有员工。

5.2 连接（JOINs）

连接是SQL中用于从两个或多个表中根据它们之间的相关列检索数据的方法。

5.2.1 内连接（INNER JOIN）

内连接返回两个表中匹配的记录。如果左表（LEFT JOIN的左侧表）的某行在右表中有匹配，则结果中包括这一行；反之亦然。

SELECT 
    Employees.Name, 
    Departments.DeptName
FROM 
    Employees
INNER JOIN 
    Departments ON Employees.DeptID = Departments.DeptID;

5.2.2 左连接（LEFT JOIN）

左连接返回左表中的所有记录，即使右表中没有匹配。如果右表中没有匹配，则结果中右表的部分将为NULL。

SELECT 
    Employees.Name, 
    Departments.DeptName
FROM 
    Employees
LEFT JOIN 
    Departments ON Employees.DeptID = Departments.DeptID;

5.2.3 右连接（RIGHT JOIN）

右连接与左连接相反，它返回右表中的所有记录，即使左表中没有匹配。

5.2.4 全连接（FULL JOIN）

全连接返回两个表中的所有记录。当某行在另一个表中没有匹配时，则另一个表的部分在结果集中为NULL。

SELECT 
    Employees.Name, 
    Departments.DeptName
FROM 
    Employees
FULL JOIN 
    Departments ON Employees.DeptID = Departments.DeptID;

5.3 窗口函数（Window Functions）

窗口函数为每一行数据执行计算，但与聚合函数不同的是，它们不会合并行来生成单一的结果；相反，它们为每个结果集的行生成一个计算结果。

5.3.1 ROW_NUMBER()

为结果集中的每一行分配一个唯一的序号。

SELECT 
    EmployeeID, 
    Name, 
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM 
    Employees;

5.3.2 RANK() 和 DENSE_RANK()

RANK() 和 DENSE_RANK() 类似于 ROW_NUMBER()，但它们在处理并列排名时有所不同。RANK() 在出现并列时会跳过随后的排名号，而DENSE_RANK() 则不会。

5.3.3 SUM() OVER()

用于计算分区内的累加和。

SELECT 
    DeptID, 
    EmployeeID, 
    Salary,
    SUM(Salary) OVER (PARTITION BY DeptID) AS DeptTotalSalary
FROM 
    Employees;

5.4 公用表表达式（Common Table Expressions, CTEs）

CTE是一个临时的结果集，它在SQL语句的执行期间存在，只用于该语句的范围内。CTE使得复杂的查询更容易编写、理解和维护。

WITH EmployeeDeptSales AS (
    SELECT 
        DeptID, 
        SUM(Sales) AS TotalSales
    FROM 
        EmployeeSales
    GROUP BY 
        DeptID
)
SELECT 
    D.DeptName, 
    EDS.TotalSales
FROM 
    Departments D
JOIN 
    EmployeeDeptSales EDS ON D.DeptID = EDS.DeptID
ORDER BY 
    EDS.TotalSales DESC;

在上面的例子中，CTE EmployeeDeptSales 首先计算了每个部门的总销售额，然后这个结果集被用来与 Departments 表进行连接，以获取每个部门的名称和总销售额。

结论

复杂查询是SQL查询的进阶技能，它们允许你以灵活而强大的方式从数据库中检索和分析数据。通过掌握子查询、连接、窗口函数和公用表表达式，你可以解决更加复杂的数据检索和分析问题。在实际应用中，根据具体需求灵活运用这些技术，可以极大地提高数据处理的效率和准确性。随着你对这些概念的深入理解和实践，你将能够编写出更加高效、可读性更强的SQL查询语句。