SQLite子查询与复合查询的应用

SQLite子查询基础

在SQLite中，子查询是指嵌套在另一个查询（主查询）中的查询。子查询可以出现在 SELECT、FROM、WHERE、HAVING 等子句中。它为我们提供了一种强大的方式来基于复杂的条件获取数据。

子查询在 WHERE 子句中的应用

当我们需要基于另一个查询的结果来过滤数据时，在 WHERE 子句中使用子查询是很常见的场景。例如，假设我们有两个表，Employees 表存储员工信息，Departments 表存储部门信息。Employees 表中有 EmployeeID、Name、DepartmentID 等字段，Departments 表中有 DepartmentID、DepartmentName 等字段。我们想要找出所有在 “Sales” 部门的员工。

首先创建并填充表：

-- 创建Departments表
CREATE TABLE Departments (
    DepartmentID INTEGER PRIMARY KEY,
    DepartmentName TEXT
);
-- 创建Employees表
CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    Name TEXT,
    DepartmentID INTEGER,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
-- 插入数据到Departments表
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (1, 'Sales');
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (2, 'Marketing');
-- 插入数据到Employees表
INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (1, 'Alice', 1);
INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (2, 'Bob', 2);

然后使用子查询来获取 “Sales” 部门的员工：

SELECT Name
FROM Employees
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Sales'
);

在这个例子中，子查询 SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales' 先执行，它返回 “Sales” 部门的 DepartmentID。然后主查询 SELECT Name FROM Employees WHERE DepartmentID = <子查询结果> 使用这个 DepartmentID 来筛选出相应部门的员工。

子查询在 SELECT 子句中的应用

子查询也可以出现在 SELECT 子句中。例如，我们想要获取每个员工及其所在部门的员工数量。

SELECT 
    e.Name,
    (
        SELECT COUNT(*) 
        FROM Employees sub_e
        WHERE sub_e.DepartmentID = e.DepartmentID
    ) AS EmployeeCount
FROM Employees e;

这里，子查询 SELECT COUNT(*) FROM Employees sub_e WHERE sub_e.DepartmentID = e.DepartmentID 为每个员工计算其所在部门的员工总数。主查询则选择员工姓名以及对应的员工数量。需要注意的是，在子查询中，我们使用了别名 sub_e 来避免与主查询中的 Employees 表混淆。

相关子查询

相关子查询是一种特殊的子查询，它的执行依赖于主查询中的值。与之前的子查询不同，相关子查询不能独立运行，因为它需要主查询提供上下文。

示例：查找每个部门工资高于平均工资的员工

假设 Employees 表中有 EmployeeID、Name、DepartmentID 和 Salary 字段。我们要找出每个部门中工资高于该部门平均工资的员工。

SELECT e1.Name, e1.Salary
FROM Employees e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DepartmentID = e1.DepartmentID
);

在这个查询中，对于主查询 Employees 表中的每一行 e1，子查询 SELECT AVG(e2.Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID 计算出相应部门的平均工资。然后主查询根据子查询返回的平均工资来筛选出工资高于平均工资的员工。

相关子查询的执行过程

1. 主查询从 Employees 表中选择第一行数据，记为 e1。
2. 子查询使用 e1 的 DepartmentID 计算该部门的平均工资。
3. 主查询根据子查询返回的平均工资判断 e1 的工资是否高于平均工资，如果是，则将 e1 的姓名和工资包含在结果集中。
4. 主查询继续选择 Employees 表中的下一行数据，重复步骤 2 和 3，直到处理完所有行。

嵌套子查询

嵌套子查询是指在一个子查询中再嵌套另一个子查询。这种结构可以用于处理非常复杂的数据筛选逻辑。

示例：查找所在部门平均工资高于全公司平均工资的员工

SELECT e.Name
FROM Employees e
WHERE e.DepartmentID IN (
    SELECT DepartmentID
    FROM (
        SELECT DepartmentID, AVG(Salary) AS DeptAvgSalary
        FROM Employees
        GROUP BY DepartmentID
        HAVING AVG(Salary) > (
            SELECT AVG(Salary)
            FROM Employees
        )
    ) sub1
);

1. 最内层子查询 SELECT AVG(Salary) FROM Employees 计算全公司的平均工资。
2. 中间层子查询 SELECT DepartmentID, AVG(Salary) AS DeptAvgSalary FROM Employees GROUP BY DepartmentID HAVING AVG(Salary) > <全公司平均工资> 找出平均工资高于全公司平均工资的部门。
3. 最外层子查询 SELECT e.Name FROM Employees e WHERE e.DepartmentID IN (<中间层子查询结果>) 找出属于这些部门的员工姓名。

SQLite复合查询

复合查询是将多个 SELECT 语句的结果合并在一起。SQLite 支持三种主要的复合查询操作符：UNION、UNION ALL 和 EXCEPT（在某些版本中支持 INTERSECT）。

UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并去除重复行。例如，我们有两个表 Table1 和 Table2，它们结构相同，都有 ID 和 Value 字段。

-- 创建Table1表
CREATE TABLE Table1 (
    ID INTEGER PRIMARY KEY,
    Value TEXT
);
-- 创建Table2表
CREATE TABLE Table2 (
    ID INTEGER PRIMARY KEY,
    Value TEXT
);
-- 插入数据到Table1表
INSERT INTO Table1 (ID, Value) VALUES (1, 'A');
INSERT INTO Table1 (ID, Value) VALUES (2, 'B');
-- 插入数据到Table2表
INSERT INTO Table2 (ID, Value) VALUES (2, 'B');
INSERT INTO Table2 (ID, Value) VALUES (3, 'C');

使用 UNION 合并两个表的结果：

SELECT ID, Value
FROM Table1
UNION
SELECT ID, Value
FROM Table2;

在这个例子中，UNION 操作符将 Table1 和 Table2 的结果合并，并去除重复行。结果集中将包含 (1, 'A')、(2, 'B') 和 (3, 'C')。

UNION ALL 操作符

UNION ALL 与 UNION 类似，但它不会去除重复行。继续以上面的 Table1 和 Table2 为例：

SELECT ID, Value
FROM Table1
UNION ALL
SELECT ID, Value
FROM Table2;

结果集中将包含 (1, 'A')、(2, 'B')、(2, 'B') 和 (3, 'C')，(2, 'B') 出现了两次，因为 UNION ALL 保留所有行。

EXCEPT 操作符（部分版本支持）

EXCEPT 操作符用于返回在第一个 SELECT 语句结果集中但不在第二个 SELECT 语句结果集中的行。假设我们还是使用 Table1 和 Table2：

SELECT ID, Value
FROM Table1
EXCEPT
SELECT ID, Value
FROM Table2;

结果将只包含 (1, 'A')，因为这是 Table1 中有而 Table2 中没有的行。

INTERSECT 操作符（部分版本支持）

INTERSECT 操作符用于返回在两个 SELECT 语句结果集中都存在的行。

SELECT ID, Value
FROM Table1
INTERSECT
SELECT ID, Value
FROM Table2;

结果将包含 (2, 'B')，因为这是 Table1 和 Table2 中都有的行。

复合查询与子查询的结合应用

在实际应用中，我们经常需要将复合查询与子查询结合使用，以满足复杂的数据处理需求。

示例：找出在特定部门工作且工资高于平均工资的员工

假设我们有 Employees 表和 Departments 表，Employees 表有 EmployeeID、Name、DepartmentID、Salary 字段，Departments 表有 DepartmentID、DepartmentName 字段。我们要找出在 “Sales” 部门工作且工资高于全公司平均工资的员工。

SELECT e.Name
FROM Employees e
WHERE e.DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Sales'
)
AND e.Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

这里我们先使用子查询找出 “Sales” 部门的 DepartmentID，同时使用另一个子查询计算全公司的平均工资。主查询根据这两个子查询的结果筛选出符合条件的员工姓名。

示例：结合复合查询与子查询统计员工信息

假设我们有三个表：Employees（包含 EmployeeID、Name、DepartmentID、Salary）、Departments（包含 DepartmentID、DepartmentName）和 Projects（包含 ProjectID、ProjectName、EmployeeID）。我们要统计参与了 “Project X” 项目且工资高于所在部门平均工资的员工数量，以及没有参与 “Project X” 项目但工资高于全公司平均工资的员工数量。

-- 参与“Project X”项目且工资高于所在部门平均工资的员工数量
SELECT COUNT(*) AS ParticipatedCount
FROM Employees e
JOIN Projects p ON e.EmployeeID = p.EmployeeID
WHERE p.ProjectName = 'Project X'
AND e.Salary > (
    SELECT AVG(Salary)
    FROM Employees sub_e
    WHERE sub_e.DepartmentID = e.DepartmentID
)
UNION ALL
-- 没有参与“Project X”项目但工资高于全公司平均工资的员工数量
SELECT COUNT(*) AS NotParticipatedCount
FROM Employees e
LEFT JOIN Projects p ON e.EmployeeID = p.EmployeeID
WHERE p.ProjectID IS NULL
AND e.Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

1. 第一个 SELECT 语句使用子查询计算员工所在部门的平均工资，并筛选出参与 “Project X” 项目且工资高于所在部门平均工资的员工，然后统计数量。
2. 第二个 SELECT 语句使用 LEFT JOIN 来找出没有参与 “Project X” 项目的员工，并使用子查询计算全公司平均工资，筛选出工资高于全公司平均工资的员工，然后统计数量。
3. UNION ALL 将这两个统计结果合并在一起展示。

性能优化考虑

在使用子查询和复合查询时，性能是一个重要的考虑因素。

子查询性能优化

1. 避免多层嵌套：过多的嵌套子查询会使查询变得复杂，并且可能导致性能下降。尽量简化嵌套层次，通过其他方式（如 JOIN）来实现相同的逻辑。例如，前面查找所在部门平均工资高于全公司平均工资的员工的例子，也可以通过 JOIN 来实现：

SELECT e.Name
FROM Employees e
JOIN (
    SELECT DepartmentID
    FROM Employees
    GROUP BY DepartmentID
    HAVING AVG(Salary) > (
        SELECT AVG(Salary)
        FROM Employees
    )
) sub ON e.DepartmentID = sub.DepartmentID;

2. 合理使用索引：确保子查询和主查询中涉及的条件字段上有适当的索引。例如，在前面查找 “Sales” 部门员工的例子中，如果在 Departments 表的 DepartmentName 字段和 Employees 表的 DepartmentID 字段上创建索引，可以显著提高查询性能。

CREATE INDEX idx_department_name ON Departments(DepartmentName);
CREATE INDEX idx_department_id ON Employees(DepartmentID);

复合查询性能优化

1. 使用 UNION ALL 替代 UNION：如果可以确定结果集中不会有重复行，尽量使用 UNION ALL 而不是 UNION。因为 UNION 需要去除重复行，这会增加额外的计算开销。
2. 优化子查询部分：如果复合查询中包含子查询，同样要对这些子查询进行性能优化，遵循子查询性能优化的原则。

总结

SQLite的子查询和复合查询为我们提供了强大的数据处理能力。子查询允许我们在查询中嵌套其他查询，以实现复杂的条件筛选和数据计算。相关子查询、嵌套子查询等不同类型的子查询适用于不同的业务场景。复合查询则通过 UNION、UNION ALL、EXCEPT 等操作符将多个 SELECT 语句的结果合并，满足多样化的数据展示和分析需求。在实际应用中，我们要注意性能优化，合理使用索引、避免过度嵌套等，以确保数据库查询的高效执行。通过熟练掌握子查询和复合查询的应用，我们能够更好地处理SQLite数据库中的数据，为各种应用程序提供准确、高效的数据支持。无论是小型应用还是大型项目，这些技术都是数据库开发中不可或缺的部分。

希望通过以上内容，你对SQLite的子查询与复合查询的应用有了更深入的理解和掌握。在实际项目中不断实践和优化，将能更好地发挥这些技术的优势。