SQL 递归查询 与 调优 与 group_concat()

SQL 递归 RECURSIVE 查询

1. Oracle 通过 START WITH 和 CONNECT BY 子句实现

SELECT
    a.codeName,
    a.lv,
    a.code,
    b.code,
    b.code_parent
FROM
    table_a a
    LEFT JOIN table_b b ON a.code = b.code
WHERE
    a.lv IN ('01', '02', '03')
    START WITH b.code_parent = '0000'
    CONNECT BY NOCYCLE PRIOR b.code = b.code_parent -- 上一条的code，当作下一条code_parent条件的值
ORDER BY b.code ASC, a.lv ASC

• 解释

1. start with 起点查询
2. connect by [NOCYCLE] [PRIOR] 父节点与子节点的关系
3. nocycle 当出现父节点与子节点的值相同时，这个关键值可以避免死循环
4. prior 该关键值后面出现的字段标识为父节点
5. 其中connect by PRIOR b.code = b.code_parent 可以交换前后顺序, 写成connect by b.code_parent = PRIOR b.code

2. MySQL 使用 WITH RECURSIVE 语法 (递归 CTE)

MySQL 8.0 或更高版本支持

更多 WITH 语法相关: MySQL 的 WITH 语法

WITH RECURSIVE employee_hierarchy AS (
    -- 基本查询：找到起始点（根节点）
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE name = 'Alice'
    
    UNION ALL
    
    -- 递归查询：找到所有下属
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy hierarchy ON e.manager_id = hierarchy.employee_id
)
-- 已排好序, 从上到下, 根节点到子节点
SELECT * FROM employee_hierarchy;

-- 数据准备
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, NAME, MANAGER_ID) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 1), (4, 'David', 2), (5, 'Eva', 3), (6, 'Alice', NULL), (7, 'Bob', 1), (8, 'Charlie', 7), (9, 'David', 6), (10, 'Eva', 3);

• 解释

1. 基本查询：第一个 SELECT 查找起始员工（如 Alice），这是递归的起点
2. 递归部分：第二个 SELECT 通过自连接 employees 表，查找所有由 employee_hierarchy 中当前员工管理的员工
3. UNION ALL：将两个查询的结果组合在一起
4. 最终选择：最后从 employee_hierarchy 中选择所有结果

递归 和 迭代 的区别：

• 递归是一个树结构，每个分支都探究到最远，发现无法继续的时候往回走。每个节点只会访问一次。过程中，不断缩小。
• 迭代是一个循环链表结构，每次迭代都是一个环，所有节点都会访问，然后不断循环，每个节点都会被循环访问。过程由远到近，不断往前滚，最终接近目标。（中间会调整）

---

调优

索引失效情况

模型数 空运最快

1. 模糊查询
2. 类型转换
3. 函数
4. 空值判读
5. 数学运算
6. 最左匹配
7. 全表扫描更快

where 语句执行顺序

从 右到左，直至最后一个条件, 依次向前推进判去判断过滤数据，

调优步骤

1. 运算符(与或非): 优先级越高越往后 ( and | or | != 高往后)
2. 同运算符内, 字段筛的 数据范围 越大越往后

sql 语言的执行顺序

1. from
2. join
3. where
4. group
5. having
6. select
7. order
8. limit

精准的模糊查询

使用 下划线_ 来匹配单个字

select * from user_table where name like concat("张","_","先","_");
-- 可以匹配到：张三先生；下划线是占位符，精准查询4位字符的内容；

合并分组 group_concat()

• 定义: GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.

1. 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

2. 语法：There are 3 clauses of GROUP_CONCAT

• DISTINCT: This clause eliminates the repeated values in the result.

• ORDER BY: This clause concatenates the values after sorting them.

• SEPARATOR: This clause automatically separates the values by , operator. If we wish to separate the values by a different operator, we would pass the operator in the string literal.

group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])

实例

按 id 和 name 分类，查询课程的总和，同时显示所有的课程

• sql如下:

SELECT 
	id, 
	name,
	group_concat(cla separator '-') cla,
	sum(num) num
FROM st 
GROUP BY id,name;

• 查询结果如图：