sqlite with 区别 with recursive|极客笔记

sqlite with 区别 with recursive

在使用SQLite数据库时，我们经常会用到WITH子句来创建临时视图或子查询。WITH子句允许我们在一个查询中定义一个临时表，然后在该查询的其余部分中引用该临时表。这样可以使查询更加清晰和易于理解。除了普通的WITH子句外，在SQLite中还提供了WITH RECURSIVE子句，用于处理递归查询。

本文将详细介绍在SQLite中使用WITH和WITH RECURSIVE的区别和用法，并通过示例代码来演示它们的使用。

1. `WITH`子句

WITH子句的基本语法如下所示：

WITH table_name AS (
    -- 查询语句
)
SELECT * 
FROM table_name;

在这里，table_name是我们为临时表起的名字，在AS关键字后面的括号中可以写入具体的查询语句。这样我们就可以在SELECT语句中引用这个临时表。

示例代码如下：

WITH department AS (
    SELECT *
    FROM employees
    WHERE department = 'HR'
)
SELECT * 
FROM department;

上面的示例中，我们将所有部门为’HR’的员工存储在临时表department中，并通过SELECT语句从该临时表中查询数据。

2. `WITH RECURSIVE`子句

WITH RECURSIVE子句用于处理具有递归结构的查询。通常，在处理组织架构、层次关系等复杂数据时，很容易需要使用递归查询。WITH RECURSIVE可以帮助我们处理这种类型的查询。

WITH RECURSIVE子句的基本语法如下所示：

WITH RECURSIVE table_name AS (
    -- 初始查询语句
    UNION ALL
    -- 递归查询语句
)
SELECT * 
FROM table_name;

在WITH RECURSIVE子句中，我们需要提供两部分内容：

初始查询语句：该查询语句用于确定初始的结果集，并在后续的递归查询中不断迭代。
递归查询语句：该查询语句用于筛选出符合递归条件的记录，并与初始结果集进行联接操作。

示例代码如下：

WITH RECURSIVE employees_under_manager AS (
    SELECT * 
    FROM employees
    WHERE manager_id = 1
    UNION ALL
    SELECT e.*
    FROM employees AS e
    JOIN employees_under_manager AS em
    ON e.manager_id = em.employee_id
)
SELECT * 
FROM employees_under_manager;

上面的示例中，我们使用WITH RECURSIVE子句来查找特定经理下的所有下属。初始查询语句选取了经理ID为1的员工作为初始结果集，然后递归查询语句通过联接操作不断往下查找其下属员工，直到所有下属员工都被查找出来。

3. 区别与应用场景

WITH子句和WITH RECURSIVE子句之间的最大区别在于处理递归查询。如果你在处理普通的临时表查询时，可以使用WITH子句；而如果你需要处理涉及递归结构的查询，应使用WITH RECURSIVE子句。

适用WITH子句的场景：

简单的临时表查询
普通的多步查询
不需要递归的情况

适用WITH RECURSIVE子句的场景：

处理具有递归结构的查询
处理组织架构、层次关系等复杂数据
需要迭代查询的场景

4. 示例代码演示

为了更加直观地理解WITH和WITH RECURSIVE子句的用法，我们通过一个简单的示例代码来演示它们的运行结果。

考虑以下名为employees的表，包含员工的ID、姓名和经理ID等信息：

employee_id	name	manager_id
1	Alice	null
2	Bob	1
3	Carol	1
4	David	2
5	Eve	2
6	Frank	4

使用`WITH`子句查询特定部门员工

WITH department AS (
    SELECT *
    FROM employees
    WHERE manager_id = 1
)
SELECT * 
FROM department;

运行结果如下：

employee_id	name	manager_id
2	Bob	1
3	Carol	1

使用`WITH RECURSIVE`子句查询特定经理下的所有下属员工

WITH RECURSIVE employees_under_manager AS (
    SELECT * 
    FROM employees
    WHERE manager_id = 1
    UNION ALL
    SELECT e.*
    FROM employees AS e
    JOIN employees_under_manager AS em
    ON e.manager_id = em.employee_id
)
SELECT * 
FROM employees_under_manager;

运行结果如下：