sql父子查询

分析sql过程

启发：https://blog.csdn.net/weixin_43703769/article/details/109254934

预热

1. 分析下面的sql的执行流程

SELECT @ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM chat WHERE FIND_IN_SET( pid, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL
FROM
    chat, ( SELECT @ids := '1', @l := 0 ) b
WHERE
    @ids IS NOT NULL 

这段SQL语句是一个递归查询，用于在表格 chat
中查找具有父子关系的记录，并计算每个记录的层级（LEVEL）。这里使用了用户自定义的变量（@ids和@l）来执行递归操作。以下是这段SQL语句的执行流程：

1. FROM 子句：首先，SQL引擎处理 FROM 子句，它从 chat 表中检索数据，并将其与一个子查询（(SELECT @ids := ‘1’, @l := 0)
   ）联接在一起。这个子查询初始化了两个用户自定义变量，@ids设置为 ‘1’，@l设置为 0。
2. WHERE 子句：WHERE 子句中的条件 @ids IS NOT NULL 用于控制递归的终止条件。只要 @ids 不为 NULL，查询就会继续执行。
3. SELECT 子句：SELECT 子句包含了三个部分：

• @ids AS _ids：将当前的 @ids 值赋给 _ids。
• (SELECT @ids := GROUP_CONCAT( id ) FROM chat WHERE FIND_IN_SET( pid, @ids ) ) AS cids：在这一部分，GROUP_CONCAT 函数用于将与
  @ids 中的 pid 值相关的 id 值连接成一个逗号分隔的字符串。然后，这个字符串被赋给 @ids，以便在下一次迭代中使用。这将更新
  @ids 变量以包含新的 id 值。
• @l := @l + 1 AS LEVEL：将 @l 增加 1，并将结果赋给 LEVEL 变量，用于跟踪当前记录的层级。

4. 执行递归：在每次迭代中，@ids 的值都会更新，通过查找满足 FIND_IN_SET(pid, @ids) 条件的记录，将与 @ids 中的 pid
   值相关的子记录添加到结果集中。这个过程将一直进行，直到不再有满足条件的记录，此时 @ids 变量将为 NULL，查询结束。
5. 结果集：查询的结果集将包括 _ids（包含每个记录的初始 id 值）、cids（包含每个记录的关联 id 值列表），以及 LEVEL（每个记录的层级）。
   这个SQL查询执行了一种递归操作，用于查找具有父子关系的记录，并构建一个层次结构。在每次迭代中，通过更新 @ids 变量，它查找与当前
   @ids 中的 pid 值相关的子记录。这允许你在结果中获得每个记录的层级信息。

用于分析的数据

chat_summary表数据

sql查询

create table ttt as
SELECT DISTINCT c1.LEVEL - 1 LEVEL,
                c2.id,
                c2.pid,
                c2.ask
FROM (SELECT @ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM chat WHERE FIND_IN_SET( pid, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL
      FROM
          chat, ( SELECT @ids := '1', @l := 0 ) b
      WHERE
          @ids IS NOT NULL) c1,
     chat_summary c2
WHERE FIND_IN_SET(c2.id, c1._ids)
ORDER BY LEVEL,
         id

查询ttt表结果如下：

sql查询

select max(id), ask, level
from ttt
group by level

查询结果如下：

此时已经符合预期！！！

sql查询

select find_in_set("abc", "123,abc,456")

结果如下：

探寻测试之路

tes表内容如下

sql查询

SELECT *
FROM tes
WHERE FIND_IN_SET('Music', interests) > 0;

查询结果如下:

sql查询

SELECT @ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM chat WHERE FIND_IN_SET( pid, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL
FROM
    chat, ( SELECT @ids := '1', @l := 0 ) b
WHERE
    @ids IS NOT NULL 

查询结果如下：

sql查询

SELECT @ids := '0', @l := 0

查询结果如下：

最终优化sql

SELECT d.*
FROM (
         SELECT
             @recursion_condition_var AS parent_id,
             @recursion_condition_var := (
            SELECT @parent_id := MAX(id)
            FROM chat_summary
            WHERE pid = @parent_id
        ) AS max_child_id,
        @level := @level + 1 AS LEVEL
         FROM chat_summary a,
             (SELECT @parent_id := 1, @level := 0, @recursion_condition_var := @parent_id) b
         WHERE @parent_id IS NOT NULL AND @recursion_condition_var IS NOT NULL
     ) c
         LEFT JOIN chat_summary d ON parent_id = d.id;

最终结果：

补充

准备下列数据tmp表

有下列sql语句

SELECT a.id, @ids AS _ids,
		( SELECT @ids := GROUP_CONCAT( id ) FROM tmp WHERE FIND_IN_SET( pid, @ids ) ) AS cids,
		@l := @l + 1 AS LEVEL
FROM
    tmp a, ( SELECT @ids := '1', @l := 0 ) b
WHERE
    @ids IS NOT NULL

执行过程

1. 先执行子查询，SELECT @ids := ‘1’, @l := 0，也就是确认了表b的所有内容，此时b表中只有一条记录，即

2. 此时b表中只有一条记录，from的结果是是a和b的自然连接结果，所以确认了总的查询结果，就是7条，即由a的行数决定总的查询结果行数,
   执行执行SELECT @ids := GROUP_CONCAT( id ) FROM tmp WHERE FIND_IN_SET( pid, @ids )，把pid为1的行id的值拼接起来，即2,4,5，此时@ids的值变成了2,4,5，
   a表的第一行与b的第一行（b始终只有一行）进行自然连接的结果是

3. 重复以上步骤，a表的第二行与b的第一行进行自然连接，结果是

4. 让a的每一行与b的每一行进行自然连接，结果是

5. 加上where条件后，筛选出_ids不为null的值